Parse comma separated input string in SQL
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
Capitalise first character of every word
Commonly used string operation in SQL
Parse comma separated input string in SQL
Ways of generating random password or string
SQL-Removing numbers in a string
Advanced Strings Format for numbers
Different string functions in SQL-Part 2
Need and advantages of using computed columns
Advanced Datetime Format for string
Different string functions in SQL-Part 1
Post Comment