delete from the end of the string

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

General SQL Server Forums

New to SQL Server Programming

delete from the end of the string

Author

Topic

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-05-14 : 19:30:50

Hi.I would like to have a query which delete ',' from the end of the string.
the fields are like this:

Categories: Batteries,ATTACHMENTS,
Categories: Default,ACCESSORIES
Categories:Magazines,

plz help.
thnks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-14 : 19:38:46

There is probably a better way of doing this but

declare @var varchar(50)

set @var = 'Batteries,ATTACHMENTS,'

select case
when charindex(',',reverse(@var)) = 1
then reverse(substring(reverse(@var),2,100))
else @var
end

Jim

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-05-14 : 19:49:05

thanks for reply.
but it didn't work
I have 20000 records.some of them have , at the end some of them not.
the length & the values of the string are different.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-14 : 22:35:13

As long as you don't have any tildes (~) in your column:

SELECT replace(replace(rtrim(replace(replace(myColumn, ' ', '~'), ',', ' ')), ' ', ','), '~', ' ') FROM myTable

If you do have tildes then replace them in the code with a character you know is NOT in that column.

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-05-18 : 12:37:22

thanks
worked :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources