substring function
-
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.
-
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.
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 -
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.
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 )
-
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.
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) endBTW, 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."
-
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) endBTW, 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."
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, 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 Levinsonhttp://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.
-
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.
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.
-
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.
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.
-
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.
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.