substring function

[Sonia Gupta]

declare @exp varchar(100) set @exp = '12,13,14' declare @start int declare @substring varchar(20) set @start = 1 while(@start <> 0) begin set @start = charindex(',' , @exp , @start) if(@start = 0) return print @start --print substring(@exp , @start , set @start = @start + 1 end i want the following output 12 13 14 how should i get it

Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT.

[Vasudevan Deepak Kumar]

Sonia, Did you consider posting in SQL/ADO/ADO.NET forum which is a more befiting place for this query than here? http://www.codeproject.com/script/comments/forums.asp?forumid=1725[^]

Vasudevan Deepak Kumar Personal Homepage
Tech Gossips
A pessimist sees only the dark side of the clouds, and mopes; a philosopher sees both sides, and shrugs; an optimist doesn't see the clouds at all - he's walking on them. --Leonard Louis Levinson

[Christian Graus]

One good way is to pass XML and use OpenXML instead of string mashing. But, it's true you're in the wrong forum.

Christian Graus - Microsoft MVP - C++ "also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )

[Michael Sync]

Check-out this SQL split function http://www.devx.com/tips/Tip/20009 Here is my modified (0.1%) code. DECLARE @String nvarchar (4000) DECLARE @Delimiter nvarchar (10) SET @String = '12,13,14' SET @Delimiter = ',' declare @NextString nvarchar(4000) declare @Pos int declare @NextPos int declare @CommaCheck nvarchar(1) --Initialize set @NextString = '' set @CommaCheck = right(@String,1) --Check for trailing Comma, if not exists, INSERT --if (@CommaCheck <> @Delimiter ) set @String = @String + @Delimiter --Get position of first Comma set @Pos = charindex(@Delimiter,@String) set @NextPos = 1 --Loop while there is still a comma in the String of levels while (@pos <> 0) begin set @NextString = substring(@String,1,@Pos - 1) PRINT @NextString set @String = substring(@String,@pos +1,len(@String)) set @NextPos = @Pos set @pos = charindex(@Delimiter,@String) end BTW, this is ASP.NET forum. (You have been here for long time so you know that, right? ):)

Thanks and Regards, Michael Sync ( Blog: http://michaelsync.net) "Please vote to let me (and others) know if this answer helped you or not. A 5 vote tells people that your question has been answered successfully and that I've pitched it at just the right level. Thanks."

[Sonia Gupta]

http://www.codeproject.com/script/comments/forums.asp?msg=2326415&forumid=1725#xx2326415xx[^]

Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT.

[Sonia Gupta]

http://www.codeproject.com/script/comments/forums.asp?msg=2326415&forumid=1725#xx2326415xx[^]

Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT.

[InsDev]

declare @exp varchar(100) set @exp = '12,13,14' declare @start int declare @substring varchar(20) set @start = 1 while(@start <> 0) begin set @start = charindex(',' , @exp , @start) if(@start = 0) Begin select substring(@exp,@start+1,2) return end select substring(@exp,@start+1,2) set @start = @start + 1 end Devjit Das.

[Sonia Gupta]

InsDev wrote:

if(@start = 0) Begin select substring(@exp,@start+1,2) return end

it is not certain wherther the length will be = 2 or more then 2 if i replace 2 with len(@exp) again bug starts to occur

Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT.

[Sonia Gupta]

What's the problem in the following declare @exp varchar(100) set @exp = '12,13,14' declare @start int declare @pos int declare @substring varchar(20) set @pos = 1 set @start = 1 while(@pos <> 0) begin set @pos = charindex(',' , @exp , @start) if(@pos = 0) begin print substring(@exp , @start , len(@exp)) return end print substring(@exp , @start , @pos -1) set @start = @pos + 1 end

Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT.