Funtion to split any string contant in Microsoft SQL Server

Posted: August 8, 2009 in SQL
Tags: , , ,

After a very long time I am posting this stuff…

I have noticed that on day to day activity with MS SQL, one requires to split any string constant every now and then and so I have decided to make a post of it.

Please create below SQL function into your database and then you can use this to split any string constant with any separator you specified.


CREATE function [dbo].[fun_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

You can use this function like example given below:
SELECT Items FROM dbo.fun_Split('1,2,3,4',',')

This will return a table with Column “Items” where you can find each comma separated values in different rows.

Happy Scripting!

Advertisements
Comments
  1. HEEEEELP!!!!!
    _______________________________________________

    While this DOES work…

    SELECT * FROM DBO.SPLIT (‘Alcoholism, Arthritis, Acid Reflux / GERD, Colon and Rectal Cancer, Breast Cancer, Diabetes, Heart Disease, Depression, High Cholesterol, High Blood Pressure, Lung Cancer’,’,’)
    _______________________________________________

    This does NOT work…

    DECLARE @LIST VARCHAR(MAX)
    SET @LIST=(SELECT “‘”+MEDICALHISTORYLIST+”‘,’,'” FROM EMRdb WHERE VISITID=1)
    SELECT @LIST
    SELECT * FROM DBO.SPLIT (@LIST)

  2. p.s.,

    Error: Msg 313, Level 16, State 3, Line 5
    An insufficient number of arguments were supplied for the procedure or function DBO.SPLIT.

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