Function to split a string which is delimited by comma or other delimiter.
We often come across a problem when the all values needed are supplied as a list in a single string, generally separated by comma, hyphen, blank space or other delimiter. What is required is to split the sting and extract each value from it for us to be of any use in SQL statements.
Eg.: ‘C104, C584, C875, C946’
Following is the function dbo.Split1, which will take two inputs – string to be split and the delimiter used in that string. It will return the all the components of the string in form of table.
ALTER FUNCTION [dbo].[Split1]
(
@List NVARCHAR(1000), — String of values
@SplitOn NVARCHAR(5) – delimiter value
)
RETURNS @RtnValue TABLE
(
Id INT IDENTITY(1,1),
Component NVARCHAR(50)
)
AS
BEGIN
WHILE (CHARINDEX(@SplitOn,@List)>0)
BEGIN
INSERT INTO @RtnValue (Component)
SELECT Component = LTRIM(RTRIM(SUBSTRING(@List,1,CHARINDEX(@SplitOn,@List)-1)))
SELECT @List = SUBSTRING(@List, CHARINDEX(@SplitOn,@List)+LEN(@SplitOn),LEN(@list))
END
INSERT INTO @RtnValue (Component)
SELECT Component = LTRIM(RTRIM(@List))
RETURN
END
GO
You can directly use the above function in a simple SELECT statement as follows:
SELECT CONVERT(VARCHAR,Component) AS ‘String Components’ FROM dbo.Split1(‘one,2,teen’,‘,’)
You can also pass a string variable to the function.
SELECT CONVERT(VARCHAR,Component) AS ‘String Components’ FROM dbo.Split1(@myList,‘,’)
It is most useful as a part of WHERE IN clause, which can be part a complex SQL queries.
SELECT * FROM Customer WHERE City IN (SELECT CONVERT(VARCHAR,Component) FROM dbo.Split1(@CityList,‘,’)
Good Luck
