SQL Server – Function to split comma delimited String or List

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

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: