need help with query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

need help with query

Author

Topic

ssunny
Posting Yak Master

133 Posts

Posted - 2008-12-19 : 12:38:31

Hello Guys,

Need help with string parser. Let's say here's my input string:

abcd,abcd,abcd,abcd

As you can see a string has 3 commas (,). What I want to find out is string before last comma (,) . So in this case my output should be abcd abcd abcd

If input string is abcd abcd,abcd then output should be abcd abcd

Here's what I wrote:

declare @input varchar (500),@output varchar(500),@checkno int
set @input = 'abcd,abcd,abcd,abcd'
set @checkno = 0
select @checkno = len (@input) - len (replace(@input,',',''))
--print @checkno
if @checkno = 1
begin

select @output = substring(@input,1,charindex(',',@input,1)-1)

end

else begin

(having problem in writing this part)

end
select @output

Thanks in advance.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-20 : 10:06:41

[code]declare @input varchar (500),@output varchar(500),@checkno int
set @input = 'aaaa,bbbb,cccc,dddd'
set @checkno = 0
select @checkno = len (@input) - len (replace(@input,',',''))
--print @checkno
if @checkno = 1
begin

select @output = substring(@input,1,charindex(',',@input,1)-1)

end

else begin

--(having problem in writing this part)
select @output=parsename(replace(@input,',','.'),4)+' '
select @output=@output+parsename(replace(@input,',','.'),3)+' '
select @output=@output+parsename(replace(@input,',','.'),2)

end
select @output
[/code]

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

ssunny
Posting Yak Master

133 Posts

Posted - 2008-12-22 : 11:29:45

quote:

Originally posted by webfred

declare @input varchar (500),@output varchar(500),@checkno int
set @input = 'aaaa,bbbb,cccc,dddd'
set @checkno = 0
select @checkno = len (@input) - len (replace(@input,',',''))
--print @checkno
if @checkno = 1
begin

select @output = substring(@input,1,charindex(',',@input,1)-1)

end

else begin

--(having problem in writing this part)
select @output=parsename(replace(@input,',','.'),4)+' '
select @output=@output+parsename(replace(@input,',','.'),3)+' '
select @output=@output+parsename(replace(@input,',','.'),2)

end
select @output

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Hey Webfred,
Thanks for the reply!But your code is not working for input string
'aaaa bbbb, cccc, dddd'. For this string I should get 'aaaa bbbb cccc' but I'm getting null.
Here's what I came up with :

declare @input varchar (500),@output varchar(500),@checkno int,@temp_input varchar(500),@temp_input1 varchar(500)
set @input = 'aaaa bbbb, cccc, dddd'
select @temp_input = reverse (@input)
select @checkno = charindex (',',@temp_input,1)
select @temp_input1 = substring (@temp_input,@checkno + 1,len(@temp_input))
select @temp_input1 = reverse(@temp_input1)
select @temp_input1 = replace (@temp_input1,',','')
select @temp_input1

Thanks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-22 : 14:04:00

Sorry - i have corrected it:

declare @input varchar (500),@output varchar(500),@checkno int
---set @input = 'aaaa,bbbb,cccc,dddd'
set @input = 'aaaa bbbb, cccc, dddd'
set @checkno = 0
select @checkno = len (@input) - len (replace(@input,',',''))
--print @checkno
if @checkno = 1
begin

select @output = substring(@input,1,charindex(',',@input,1)-1)

end

else begin

--(having problem in writing this part)
select @output=isnull(parsename(replace(@input,',','.'),4)+' ','')
select @output=@output+isnull(parsename(replace(@input,',','.'),3)+' ','')
select @output=@output+isnull(parsename(replace(@input,',','.'),2),'')

end
select @output

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

ssunny
Posting Yak Master

133 Posts

Posted - 2008-12-22 : 14:49:41

quote:

Originally posted by webfred

Sorry - i have corrected it:

declare @input varchar (500),@output varchar(500),@checkno int
---set @input = 'aaaa,bbbb,cccc,dddd'
set @input = 'aaaa bbbb, cccc, dddd'
set @checkno = 0
select @checkno = len (@input) - len (replace(@input,',',''))
--print @checkno
if @checkno = 1
begin

select @output = substring(@input,1,charindex(',',@input,1)-1)

end

else begin

--(having problem in writing this part)
select @output=isnull(parsename(replace(@input,',','.'),4)+' ','')
select @output=@output+isnull(parsename(replace(@input,',','.'),3)+' ','')
select @output=@output+isnull(parsename(replace(@input,',','.'),2),'')

end
select @output

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

This works great! Thanks a lot Webfred!!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-22 : 15:11:09

Welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources