How to find the sapces or commas

Posted by Self-innovator under Sql Server on 1/10/2012 | Points: 10 | Views : 1035 | Status : [Member] | Replies : 10
Dear all,
how to find out the no of blank spaces or commas between the substring and to loop those comma seperated substring..pls find the solution.

Join Hands Change lives
Thanks & Regards
Straight Edge Society



Responses

Posted by: Sksamantaray on: 1/10/2012 [Member] Silver | Points: 25

Up
0
Down

calculate space
declare @str varchar(100)='   v'


declare @res int
select @res=LEN(@str)
print @res
declare @Ctr int=0,@Ctr2 int=0
while (@Ctr<@res)
begin
set @Ctr=@Ctr+1
if(SUBSTRING(@str,@Ctr,1)='')
begin
set @Ctr2=@Ctr2+1

end

end
print @Ctr2


Thanks,
Sanjay

Self-innovator, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksamantaray on: 1/10/2012 [Member] Silver | Points: 25

Up
0
Down
//Comma Calculate

declare @str varchar(100)='sa,,s'

declare @res int
select @res=LEN(@str)
print @res
declare @Ctr int=0,@Ctr2 int=0
while (@Ctr<@res)
begin
set @Ctr=@Ctr+1
if(SUBSTRING(@str,@Ctr,1)=',')
begin
set @Ctr2=@Ctr2+1

end

end
print @Ctr2


Thanks,
Sanjay

Self-innovator, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Self-innovator on: 1/10/2012 [Member] Bronze | Points: 25

Up
0
Down
Thanks sksamantaray...I want to loop through the substring which i've by seperating through commas..pls do it

Join Hands Change lives
Thanks & Regards
Straight Edge Society

Self-innovator, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Self-innovator on: 1/10/2012 [Member] Bronze | Points: 25

Up
0
Down
Example: based on InputStr='1+2,1+3,1+6,3+4'
by using the above concept i seperate the substring followed by comma like 1+2 1+3 1+6 3+4 based on this i will count the total substrings and from these total substrong i wanted to loop ...pls try this

Join Hands Change lives
Thanks & Regards
Straight Edge Society

Self-innovator, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksamantaray on: 1/10/2012 [Member] Silver | Points: 25

Up
0
Down
I did not get you properly.
What i understood is , you could generate 1+2 1+3 1+6 3+4
now since, 3 spaces are there you want another loop to run for 3times.
Is it?
Please let me know.
You can also put your procedure here.

Thanks,
Sanjay

Self-innovator, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Self-innovator on: 1/10/2012 [Member] Bronze | Points: 25

Up
0
Down
Consider My problem..
First i'm passing the string as @Rating= '1+2,1+3,1+4,2+4,3+7' based on your query i'm able to split the substring followed by commas..ie:in this case i got 1+2 1+3 1+4 2+4 3+7 (totally 5 substrings) now iwanteed to loop these 5 substrings where i will be using another substring to split these substring like
set @tempExp=SUBSTRING(@Rating,1,CHARINDEX(',',@Rating)-1)
set @QnCode=LEFT(@tempExp,1)
set @Rating=RIGHT(@tempExp,1)
i hope u understand it clearly pls rectify...

Join Hands Change lives
Thanks & Regards
Straight Edge Society

Self-innovator, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Self-innovator on: 1/10/2012 [Member] Bronze | Points: 25

Up
0
Down
in first substring 1+2 (first substring)
by looping i wanted to
set @QnCode=LEFT(@tempExp,1) where i will get the value as 1 which is Quetion Code
llrly...with these set @Rating=RIGHT(@tempExp,1) where i will be getting vale as 2 which is Rating

llrly..second substring 1+3
here same task i wanted it to perform

so i wanted to do it using loop

in my above exampla i've got totally 5 substrings by counting these subsstring values is 5 and by looping 5 times i wanted to split once again these substring...pls help

Join Hands Change lives
Thanks & Regards
Straight Edge Society

Self-innovator, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksamantaray on: 1/10/2012 [Member] Silver | Points: 25

Up
0
Down
--1+2 1+3 1+6 3+4 




declare @str varchar(100)='1+2 1+3 1+6 3+4'
declare @Ctr3 int=0
create table #temp(Qcode int,Rating int)


print len(@str)
while (@Ctr3<len(@str))
begin
set @Ctr3=@Ctr3+1

if(substring(@str,@Ctr3,1)='+')
begin
--print 'ctr is'
--print @ctr3
print substring(@str,@ctr3-1,1)
--print 'ss'
print substring(@str,@ctr3+1,1)

insert into #temp values(substring(@str,@ctr3-1,1),substring(@str,@ctr3+1,1))
end
end
select * from #temp
drop table #temp


Thanks,
Sanjay

Self-innovator, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Self-innovator on: 1/10/2012 [Member] Bronze | Points: 25

Up
0
Down
thanks a lot..

Join Hands Change lives
Thanks & Regards
Straight Edge Society

Self-innovator, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Niladri.biswas on: 2/8/2012 [Member] Platinum | Points: 25

Up
0
Down
How about this

Declare 

@xml as xml
,@str as varchar(100)
,@delimiter as varchar(10)
SET @str='1+2,1+3,1+6,3+4'
SET @delimiter =','
SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)

Select
QCode = left(value,patindex('%+%',value)-1)
,Rating = right(value,len(value)-patindex('%+%',value))
From
(SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as T(N))X


Best Regards,
Niladri Biswas

Self-innovator, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response