removing trailing comma's - 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 2005 Forums

Transact-SQL (2005)

removing trailing comma's

Author

Topic

buzzi
Starting Member

48 Posts

Posted - 2009-02-02 : 15:42:17

Hello all,

Can somebody please let me know how to remove the trailing commas for a data column.

Example:

Column1
angel,sri,mahi,,,
sunny,bunny,,,,
jazz,guitar,bass,strings,,,,,

output
Column1
angel,sri,mahi
sunny,bunny
jazz,guitar,bass,strings

Thanks a lot for the help

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-02 : 15:47:13

[code][code]
CREATE FUNCTION [dbo].[ufn_TrimLeadingCharacters_reversed] ( @Input VARCHAR(50), @LeadingCharacter CHAR(1) )
RETURNS VARCHAR(50)
AS
BEGIN
RETURN reverse(REPLACE(LTRIM(REPLACE(reverse(@Input), ISNULL(@LeadingCharacter, '0'), ' ')),
' ', ISNULL(@LeadingCharacter, '0')))
END[/code]

[code]select dbo.ufn_TrimLeadingCharacters_reversed('jazz,guitar,bass,strings,,,,,',',')[/code][/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-02 : 15:53:36

REPLACE(RTRIM(REPLACE(Co1, ',', CHAR(7))), CHAR(7), ',')

E 12°55'05.63"
N 56°04'39.26"

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-02 : 16:20:06

quote:
Originally posted by Peso

REPLACE(RTRIM(REPLACE(Co1, ',', CHAR(7))), CHAR(7), ',')

E 12°55'05.63"
N 56°04'39.26"

Didn't you mean to put char32 there?

select REPLACE(RTRIM(REPLACE(Col1, ',', CHAR(32))), CHAR(32), ',')

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-02 : 16:27:57

another way:

(if sakets_2000 was correct then this will retain legitimate commas within the values)

left(colum1, len(col)- patindex('%[^,]%', reverse(colum1)) + 1)

Be One with the Optimizer
TG

buzzi
Starting Member

48 Posts

Posted - 2009-02-02 : 16:28:13

Thank you all

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-02 : 16:47:22

Buzzi,

I think you are using this. Use other 2 options so you don't get that issue.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=118938

thiyagu_rind
Starting Member

46 Posts

Posted - 2009-02-03 : 01:14:46

Hi everyone...

I thing the below will be better...

declare @str varchar(500)
set @str ='jazz,guitar,bass,strings,,,,,'
select @str
select left(@str,charindex(',,',@str,0)-1)

Regards
Thiyagarajan

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-03 : 06:54:51

quote:
Originally posted by thiyagu_rind

Hi everyone...

I thing the below will be better...

declare @str varchar(500)
set @str ='jazz,guitar,bass,strings,,,,,'
select @str
select left(@str,charindex(',,',@str,0)-1)

Regards
Thiyagarajan

Why do you think it'll be better when this gives an error ??


declare @str varchar(500)
set @str ='jazz,guitar,bass,strings,'
select @str
select left(@str,charindex(',,',@str,0)-1)

thiyagu_rind
Starting Member

46 Posts

Posted - 2009-02-04 : 01:02:39

Yes you are correct...sakets_2000
i thing the below will help to fix the issue....
Let me know if you are having any issues with the below query....

declare @str varchar(500)
set @str ='jazz,guitar,bass,strings,'
select left(@str,case charindex(',,',@str,0)
when 0 then len(@str)-1
else charindex(',,',@str,0)-1
end)

Regards
Thiyagarajan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources