How to find the sapces or commas

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

//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

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

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

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

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

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

--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

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

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