Tuesday, May 30, 2006

Split function in SQL Server -- Method 1

Off late in many of the usergroups I see people enquiring about how to split data in SQL Server. I know couple of methods to achieve this. This post would explain the first method. I would write a seperate post on the other method of doing the same task.

Here we go ....



CREATE FUNCTION [dbo].[Split]
(
@String NVARCHAR(4000),
@Delimiter CHAR(1)
)
RETURNS @Results TABLE
(
Items NVARCHAR(4000)
)
AS
BEGIN
DECLARE @Index INT
DECLARE @Slice NVARCHAR(4000)
SELECT @Index = 1
IF @String Is NULL RETURN

WHILE @Index != 0
BEGIN
SELECT @Index = CHARINDEX(@Delimiter, @String)
IF @Index != 0
SELECT @Slice = LEFT(@String, @Index - 1)
ELSE
SELECT @Slice = @String

INSERT INTO @Results(Items) VALUES (@Slice)
SELECT @String = RIGHT(@String, LEN(@String) - @Index)
IF LEN(@String) = 0 BREAK
END
RETURN
END;
GO


Lets test it out:

SELECT [items] FROM [dbo].[Split] ('1,2,3,4,5,6,7,8,9,10', ',')


Sample table Structure:


CREATE TABLE testTable
(
Sno INT,
Fname VARCHAR(100),
Address VARCHAR(100)
)
GO


Make use of the SPLIT function to insert same Sno, Fname but different Address into this table.
INSERT INTO testTable ( Sno, Fname, Address )
SELECT 1, 'Vadivel', * FROM [dbo].[Split] ('xxxxx~yyyyyyy~zzzzzz','~')
GO

Lets see how over SPLIT function has performed:

SELECT Sno, Fname, Address FROM testTable
GO

Result:

1, Vadivel, xxxxx
1, Vadivel, yyyyyyy
1, Vadivel, zzzzzz
Hope this helps!

2 comments:

Pat Allan said...

Hi Vadivel

Thanks for this, it's very helpful. A couple of questions tho:

- Why have you used Select to define variables instead of Set? ie. Set @Index = 1 (I've no idea which is better, just curious).

- You're missing the @ at the following line: If Index != 0

- Instead of having the break, Why not put the following as part of the While statement:
While @Index != 0 And Len(@String) > 0

Thanks again.

Kumar said...

hi vadivel,

can you help me how to fetch record from table using split function

problem:i need to fetch record from table only if the column values does n't contain '-bb' at last of string

for eg :'gh-hj-ui-bb' should be eliminated and 'hj-jk-bb-jk' to be fetched..


thanks in advance...