substring function
-
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 i want the following output 12 13 14 But the output is 12 13,14 14 please rectify 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 @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 i want the following output 12 13 14 But the output is 12 13,14 14 please rectify it
Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT.
Is there any way you can take your input as XML and use OpenXML instead of this nasty string mashing ? '
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 )
-
Is there any way you can take your input as XML and use OpenXML instead of this nasty string mashing ? '
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 )
this is the database side processigs.actually i don;t konw , how long the string can be , 12,13,14,15.......there in the database i am saving these numbers.problem is the code i am using at database end , shows ths following output 12 13,14 14 for the 12,13,14 expression. i wanted 12 13 14 so far xml interaction is not taking place .
Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT.
-
this is the database side processigs.actually i don;t konw , how long the string can be , 12,13,14,15.......there in the database i am saving these numbers.problem is the code i am using at database end , shows ths following output 12 13,14 14 for the 12,13,14 expression. i wanted 12 13 14 so far xml interaction is not taking place .
Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT.
Can u try the following: -- Code declare @ExpList varchar (1000) , @Delimiter char (1) , @INTValue varchar (20) , @Position int begin create table #Result ( IntValue int ) set @ExpList = '10, 11, 12, 13, 14' set @Delimiter = ',' set @ExpList = ltrim (rtrim (@ExpList)) + @Delimiter set @Position = charindex (@Delimiter, @ExpList, 1) if replace (@ExpList, @Delimiter, '') <> '' begin while @Position > 0 begin set @INTValue = ltrim (rtrim (left (@ExpList, @Position -1))) if @INTValue <> '' begin insert into #Result (IntValue) values (cast (@INTValue as int)) end :) set @ExpList = right (@ExpList, len (@ExpList) - @Position) set @Position = charindex (@Delimiter, @ExpList, 1) end end select * from #Result truncate table #Result drop table #Result end go Sample OUTPUT: -------------- IntValue --------:) 10 11 12 13 14 -- End of Code Kishore.P
-
Can u try the following: -- Code declare @ExpList varchar (1000) , @Delimiter char (1) , @INTValue varchar (20) , @Position int begin create table #Result ( IntValue int ) set @ExpList = '10, 11, 12, 13, 14' set @Delimiter = ',' set @ExpList = ltrim (rtrim (@ExpList)) + @Delimiter set @Position = charindex (@Delimiter, @ExpList, 1) if replace (@ExpList, @Delimiter, '') <> '' begin while @Position > 0 begin set @INTValue = ltrim (rtrim (left (@ExpList, @Position -1))) if @INTValue <> '' begin insert into #Result (IntValue) values (cast (@INTValue as int)) end :) set @ExpList = right (@ExpList, len (@ExpList) - @Position) set @Position = charindex (@Delimiter, @ExpList, 1) end end select * from #Result truncate table #Result drop table #Result end go Sample OUTPUT: -------------- IntValue --------:) 10 11 12 13 14 -- End of Code Kishore.P
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.
-
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 i want the following output 12 13 14 But the output is 12 13,14 14 please rectify it
Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT.
http://www.codeproject.com/script/comments/forums.asp?msg=2326409&forumid=12076#xx2326409xx My answer solve your problem???
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."
-
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.
Modified your code, see the below code: declare @exp varchar(100) , @start int , @pos int , @substring varchar(20) set @exp = '12,13,14' --set @pos = 1 set @start = 1 set @pos = charindex(',' , @exp , @start) -- Added here 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 @pos = charindex(',' , @exp , @start) -- Added here set @start = @pos + 1 end go :) Kishore.P
-
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.
Sonia Gupta wrote:
print substring(@exp , @start , @pos -1)
while executing this statement 1st time, @start is 1 and @pos is 3. so 12 is printed 2nd time, @start is 4 and @pos is 6. so 13,14 is printed i.e. from 4th position 6 characters are printed
Regards KP
-
Sonia Gupta wrote:
print substring(@exp , @start , @pos -1)
while executing this statement 1st time, @start is 1 and @pos is 3. so 12 is printed 2nd time, @start is 4 and @pos is 6. so 13,14 is printed i.e. from 4th position 6 characters are printed
Regards KP
yes.ThanQ:)
Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT.