In this post we will learn to write function that splits string by a delemiter. You can use this function in a query, or stored procedure. This function gets two parameters; first parameter is the string to split, the second parameter is the delemeter character. The function returns a table has a column with values.
The Sql Split Function Code is Below
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE FUNCTION dbo.fnSqlSplit( @stringValue VARCHAR(8000), -- List of delimited items , @delemiter VARCHAR(1) = ',' -- delimiter that separates items the default value is comma ) RETURNS @List TABLE (item VARCHAR(8000)) BEGIN DECLARE @sItem VARCHAR(8000) WHILE CHARINDEX(@delemiter,@stringValue,0) <> 0 BEGIN SELECT @sItem=RTRIM(LTRIM(SUBSTRING(@stringValue,1,CHARINDEX(@delemiter,@stringValue,0)-1))), @stringValue=RTRIM(LTRIM(SUBSTRING(@stringValue,CHARINDEX(@delemiter,@stringValue,0)+LEN(@delemiter),LEN(@stringValue)))) IF LEN(@sItem) > 0 INSERT INTO @List SELECT @sItem END IF LEN(@stringValue) > 0 INSERT INTO @List SELECT @stringValue -- Put the last item in RETURN END |
When you execute the function you will get succesful message then you can use the code below.
Example1
|
1 |
Select * from dbo.fnSqlSplit('12,23,34,45',',') |
The Result of Query
Example2
|
1 |
Select * from dbo.fnSqlSplit('Amelia-Olivia-Isla-Sophia-Isabella','-') |
The Result of Query
Example3
|
1 |
Select * from dbo.fnSqlSplit('12,5;2,6;45,9;40,5;14,5',';') |
The Result of Query
TO SEE OTHER SQL MULTİSTATEMENT TABLE VALUED FUNCTİON EXAMPLES CLİCK
