[AccessD] Tip: Replacement for Split in SQL

There's a split function that I copied (can't quite remember where from), but you pass it any string with any delimiter, and it splits the string into rows.  I suppose with a bit of a work around you could get it to appear as columns:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE FUNCTION [dbo].[Split]
/* This function is used to split up multi-value parameters */
(
@ItemList NVARCHAR(4000),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))
AS
BEGIN
DECLARE @tempItemList NVARCHAR(4000)
SET @tempItemList = @ItemList
DECLARE @i INT
DECLARE @Item NVARCHAR(4000)
SET @tempItemList = REPLACE (@tempItemList, @delimiter + ' ', @delimiter)
SET @i = CHARINDEX(@delimiter, @tempItemList)
WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)
INSERT INTO @IDTable(Item) VALUES(@Item)
IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END
Then use something like 
SELECT ITEM FROM dbo.Split('123.sds.w.2321.ss','.')


Paul Hartland






Message Received: Feb 20 2008, 11:04 AM
From: "Gustav Brock" 
To: accessd at databaseadvisors.com
Cc: 
Subject: [AccessD] Tip: Replacement for Split in SQL

Hi all

In SQL Split cannot be used as SQL doesn't accept a syntax to retrieve a single value from the array Split returns.

But if you have a field with a content like:

xyz.001.1234.ab 

you can simulate Split like this to split the content into its four parts:

SELECT 
Code, 
InStr(1,[Code],".") AS Dot1, 
InStr(1+[Dot1],[Code],".") AS Dot2, 
InStr(1+[Dot2],[Code],".") AS Dot3, 
Mid([Code],1,[Dot1]-1) AS Col1, 
Mid([Code],1+[Dot1],[Dot2]-[Dot1]-1) AS Col2, 
Mid([Code],1+[Dot2],[Dot3]-[Dot2]-1) AS Col3, 
Mid([Code],1+[Dot3]) AS Col4
FROM 
tblSomeTable;

It can easily be expanded to return more columns.

/gustav


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com





Paul Hartland
paul.hartland at fsmail.net
07730 523179