Click here to check my latest exciting videos on youtube
Search Mallstuffs

Flag Counter
Spirituality, Knowledge and Entertainment


Locations of visitors to this page


Latest Articles


Move to top
Parse comma separated input string in SQL
Posted By Sarin on Apr 05, 2012     RSS Feeds     Latest Hinduism news
2949 Views

Using Arrays (Parsing Comma separated String) in SQL

You may have observed that there is nothing like array in sql. So, in the case where you want to use array type functionality, then you have to use string with delimiter.
For ex: suppose you want to pass list of users as an input to a sql stored procedure.  In such case, we have to pass string containing all such users separated by a delimiter like comma, colon, semicolon etc. The following store procedure illustrate this functionality
  
alter Proc SplitString
@inputstring varchar(255),
@Splitchar char(1)
as  
begin
declare @Currentword varchar(2000)
DECLARE @CurrentSplitCharPos int,@PrevSplitCharPos int
  
SELECT @CurrentSplitCharPos = 0,@PrevSplitCharPos = 0
  
IF OBJECT_ID('tempdb..#Result') IS NOT NULL
DROP TABLE #Result;  
  
Create TABLE #Result(
    [Number] [varchar](50) NULL)
  
IF SUBSTRING  (@inputstring, LEN  (@inputstring), 1) <> ','
BEGIN
SELECT @inputstring = @inputstring + @Splitchar
END
SELECT @CurrentSplitCharPos = CHARINDEX  (@Splitchar, @inputstring, @CurrentSplitCharPos + 1)
WHILE @CurrentSplitCharPos > 0
BEGIN
SELECT @Currentword = SUBSTRING  (@inputstring, @PrevSplitCharPos+1,  (@CurrentSplitCharPos - @PrevSplitCharPos) -1)
insert into #Result values(@Currentword)
SET @PrevSplitCharPos = @CurrentSplitCharPos
SELECT @CurrentSplitCharPos = CHARINDEX  (@Splitchar , @inputstring, @CurrentSplitCharPos + 1)
END
select * from #Result
end
  


Explanation
First input parameter will be your delimiter separated string
Second parameter will be your delimiter char
  1)    we declare the Following three variables
        @Currentword - represent the word retrieved between two split delimiter positions
@CurrentSplitCharPos-Represent the position of the split char
@PrevSplitCharPos- Represent the position of the split char in previous loop

  2)    We craete a temp table to store list of string(array) retrieved  from  delimiter seperated string. if the table  exists, then we drop it and recreate it again.
3)    append comma at the end of the input string
4)    Starting from Left, get the position of the split char in input string  
5)    Till the position of split character is greater than 0, we repeat the following steps:
  • Retrieve the word between the previous position of split char and current position of split char. for the first loop, previous position of split char will be 0 so, it will retrieve the first word.
  • we will insert this retrieved word into temp table
  • set @PrevSplitCharPos variable to point to current position of split character so that it can be used in the next loop.
  • Set @CurrentSplitCharPos variable to point to next split char position.
                
    Repeat the above steps till we reach the end of the string

      6)    return the result which will be the list of words stored in temp table
        
  • Note: Images used on this website are either a production of Bhaktivedanta Book Trust(https://www.krishna.com), Iskcon Foundation or were found in google search under "Free to use and share". If any of the images presented here violates copyright issues or infringes anyone copyright or are not under "Fair use", then please bring it to our notice. Read Disclaimer for more.

    Share this to your friends. One of your friend is waiting for your share.
    Related Articles
    Commonly used string operation in SQL
    Different string functions in SQL-Part 2
    Advanced Strings Format for numbers
    SQL-Removing numbers in a string
    Different Strings Format for Date Time
    Capitalise first character of every word
    Create property class of database table
    Parse comma separated input string in SQL
    Different string functions in SQL-Part 1
    Advanced Datetime Format for string

    Post Comment