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
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, zzzzzzHope this helps!
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
INSERT INTO testTable ( Sno, Fname, Address )
SELECT 1, 'Vadivel', * FROM [dbo].[Split] ('xxxxx~yyyyyyy~zzzzzz','~')
GO
SELECT Sno, Fname, Address FROM testTable
GO
Result:
1, Vadivel, xxxxx
1, Vadivel, yyyyyyy
1, Vadivel, zzzzzzHope this helps!
Comments
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.
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...