Experts Exchange Solution brought to you by
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
dbo.fn_ParseComma(ColumnName,0,1) -- Start at beginning (0) and go until 1st Comma
,dbo.fn_ParseComma(ColumnName,1,2) -- Start after 1st Comma until 2nd Comma
,dbo.fn_ParseComma(ColumnName,2,99) --After 2nd Comma Until End of Row
Open in new window
CREATE FUNCTION [dbo].[fn_ParseComma]
Declare @i as Int,
@j as Int,
SET @i = 1
SET @j = 0
While @i <= Len(@str)
--If Start Comma is "first character"
if @startcomma = 0 and @i = 1
SET @startpos = 1
--If End Comma is "last character"
if @endcomma = 99 and @i = len(@str)
SET @endpos = @i+1 - @startpos
If SubString(@str,@i,1) = ',' --Replace comma with other character if necessary.
SET @j = @j + 1
--If we Match Ending Comma Position
if @j = @endcomma
SET @endpos = @i - @startpos
SET @i = Len(@Str)
--If we Match Starting Comma Position
If @j = @startcomma
SET @startpos = @i+1
SET @i = @i + 1
--SET @Result = (SELECT '(' + Cast(@startpos as varchar) + ',' + Cast(@EndPos as varchar) + ')' + SubString(@str,@startpos,@endpos))
SET @Result = (SELECT SubString(@str,@startpos,@endpos))
From novice to tech pro — start learning today.
Premium members can enroll in this course at no extra cost.