removing leading and trialing commas

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 leading and trialing commas

Author

Topic

Bhavan
Starting Member

8 Posts

Posted - 2008-03-07 : 10:04:56

Hi All,

Is there a way to remove Leading & Trialing Commas (,) in a string using SQL code ? Appreciate any thoughts.

Thanks

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-07 : 10:08:29

Replace(Fieldname,',','') perhaps

Or the enhanced Parsname here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

Poor planning on your part does not constitute an emergency on my part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-07 : 10:09:25

You can use REPLACE(Field,',','') provided you dont have ',' in between.
Can you provide some sample data in which case it will be more easy to provide complete soln.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-07 : 10:12:53

I suppose you could also just use substring(field,2,len(field)-2) as well if they are leading and trailing only....

Poor planning on your part does not constitute an emergency on my part.

Bhavan
Starting Member

8 Posts

Posted - 2008-03-07 : 10:20:36

Let me post the sample date, perhaps that's easy to explain what I am after really. say, I have a string as follows.

,1810252450,1810252489,1810252539,

I want to replace the comma just begining of the string & ending of the string. Nothing should be distriubed for the commas between the string.

Thanks for the help !

Bhavan
Starting Member

8 Posts

Posted - 2008-03-07 : 10:26:37

actually, substring(field,2,len(field)-2) works absolutely fine. Very Smart solution , Visakh16 ! thanks much.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-07 : 11:55:19

SELECT REPLACE(LTRIM(RTRIM(REPLACE(Col1, ',', ' '))), ' ', ',')
FROM Table1

Will take care of any number of trailing and leading commas.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-07 : 11:57:43

quote:
Originally posted by Peso

SELECT REPLACE(LTRIM(RTRIM(REPLACE(Col1, ',', ' '))), ' ', ',')
FROM Table1

Will take care of any number of trailing and leading commas.

E 12°55'05.25"
N 56°04'39.16"

Good one

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-07 : 23:07:21

quote:
Originally posted by Peso

SELECT REPLACE(LTRIM(RTRIM(REPLACE(Col1, ',', ' '))), ' ', ',')
FROM Table1

Will take care of any number of trailing and leading commas.

E 12°55'05.25"
N 56°04'39.16"

it will break if there is space in the original string

KH
[spoiler]Time is always against us[/spoiler]

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2008-03-08 : 17:18:46