Script to convert comma separated strings into a table

 

This is a simple script that can be handy at times the input comes in the form of a delimited string in the stored procedure. Some minor changes can be done to handle integer values as well. The test code is integrated in it.

Declare
    @inList Varchar(8000),
    @current Smallint,
    @str Varchar(1000)

Declare
    @tablename Table(c1 varchar(8000))

— Some test value
Set @inList = ‘ad,ssfdsfsdf,dfsdfsf’

Begin
    While @inList <> ”
    Begin
        Set @current = CharIndex(‘,’, @inList)
        IF @current>0
            Begin
                Set @str = Left(@inList, @current-1)
                Set @inList = Right(@inList, Len(@inList)-@current)
            End
        Else
            Begin
                Set @str = @inList
                Set @inList = ”
            End
        Insert Into @tablename Values(@str)
    End
End

— Check the output values
Select * from @tablename

Advertisements
This entry was posted in SQL Server. Bookmark the permalink.

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