Introduction:
Here I will explain simple split function in SQL Server to split comma separated string into table values in SQL Server database or How to split comma separated string with custom split() function in SQL Server.
Description:
To split comma separated string in SQL Server we need to write custom method for that we need to create one function like as shown below
Custom Split function to split comma separated string into table
Once we create custom function Split() run sample query like as shown below
Here I will explain simple split function in SQL Server to split comma separated string into table values in SQL Server database or How to split comma separated string with custom split() function in SQL Server.
Description:
To split comma separated string in SQL Server we need to write custom method for that we need to create one function like as shown below
Custom Split function to split comma separated string into table
1:
2: CREATE FUNCTION dbo.Split(@String nvarchar(4000), @Delimiter
3: char(1))
4: RETURNS @Results TABLE (Items nvarchar(4000))
5: AS
6: BEGIN
7: DECLARE @INDEX INT
8: DECLARE @SLICE nvarchar(4000)
9: -- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
10: -- ERO FIRST TIME IN LOOP
11: SELECT @INDEX = 1
12: WHILE @INDEX !=0
13: BEGIN
14: -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT
15: CHARACTER
16: SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
17: -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE
18: VARIABLE
19: IF @INDEX !=0
20: SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
21: ELSE
22: SELECT @SLICE = @STRING
23: -- PUT THE ITEM INTO THE RESULTS SET
24: INSERT INTO @Results(Items) VALUES(@SLICE)
25: -- CHOP THE ITEM REMOVED OFF THE MAIN STRING
26: SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
27: -- BREAK OUT IF WE ARE DONE
28: IF LEN(@STRING) = 0 BREAK
29: END
30: RETURN
31: END
Once we create custom function Split() run sample query like as shown below
1: SELECT items FROM [dbo].[Split] ('861,739,10,1670', ',')
Once we run above query we will get output like as shown below
Output
No comments:
Post a Comment