sql server programming:
Hi! I have a varchar field called 'error_code' that looks like this ,C101,C102 etc It can also look like C100,C101,C103 depending on the validation errors. I need to remove the , when is the firt character. I tried using ltrim but it does nothing. I also tried using left(error_code) - 1 and get a conversion error. Any help would be greatly appreciated. Thanks! TG
[quoted text, click to view] TG wrote:
> Hi!
>
> I have a varchar field called 'error_code' that looks like this
>
> ,C101,C102 etc
>
> It can also look like
>
> C100,C101,C103
>
> depending on the validation errors.
>
> I need to remove the , when is the firt character.
> I tried using ltrim but it does nothing.
Just use a CASE expression to check if the first character is a comma, e.g. SELECT CASE WHEN LEFT(YourColumn, 1) = ',' THEN RIGHT(YourColumn, LEN(YourColumn) - 1) ELSE YourColumn END Chris
try this.. :) declare @a varchar(100) set @a = ',C101,C102,C103' select replace(replace('!' +@a,'!,',''),'!','') set @a = 'C101,C102,C103' select replace(replace('!' +@a,'!,',''),'!','') -- -Omnibuzz (The SQL GC) http://omnibuzz-sql.blogspot.com/
TG Take a look at REPLACE function in the BOL [quoted text, click to view] "TG" <jtammyg@yahoo.com> wrote in message
news:1153887441.444247.203100@m73g2000cwd.googlegroups.com...
> Hi!
>
> I have a varchar field called 'error_code' that looks like this
>
> ,C101,C102 etc
>
> It can also look like
>
> C100,C101,C103
>
> depending on the validation errors.
>
> I need to remove the , when is the firt character.
> I tried using ltrim but it does nothing.
>
> I also tried using left(error_code) - 1 and get a conversion error.
>
> Any help would be greatly appreciated.
>
> Thanks!
>
> TG
>
Use Case and Substring, something like: Case When Left(error_code,1) = ',' Then Substring(error_code,2,Len(error_code) - 1) Else error_code End Tom [quoted text, click to view] "TG" <jtammyg@yahoo.com> wrote in message
news:1153887441.444247.203100@m73g2000cwd.googlegroups.com...
> Hi!
>
> I have a varchar field called 'error_code' that looks like this
>
> ,C101,C102 etc
>
> It can also look like
>
> C100,C101,C103
>
> depending on the validation errors.
>
> I need to remove the , when is the firt character.
> I tried using ltrim but it does nothing.
>
> I also tried using left(error_code) - 1 and get a conversion error.
>
> Any help would be greatly appreciated.
>
> Thanks!
>
> TG
>
Thank you, tom. It works like a charm. Thank you to all who replied. TG [quoted text, click to view] Tom Cooper wrote:
> Use Case and Substring, something like:
>
> Case When Left(error_code,1) = ',' Then
> Substring(error_code,2,Len(error_code) - 1) Else error_code End
>
> Tom
>
> "TG" <jtammyg@yahoo.com> wrote in message
> news:1153887441.444247.203100@m73g2000cwd.googlegroups.com...
> > Hi!
> >
> > I have a varchar field called 'error_code' that looks like this
> >
> > ,C101,C102 etc
> >
> > It can also look like
> >
> > C100,C101,C103
> >
> > depending on the validation errors.
> >
> > I need to remove the , when is the firt character.
> > I tried using ltrim but it does nothing.
> >
> > I also tried using left(error_code) - 1 and get a conversion error.
> >
> > Any help would be greatly appreciated.
> >
> > Thanks!
> >
> > TG
> >
[quoted text, click to view] On 25 Jul 2006 21:17:21 -0700, TG wrote:
>Hi!
>
>I have a varchar field called 'error_code' that looks like this
>
>,C101,C102 etc
>
>It can also look like
>
>C100,C101,C103
>
>depending on the validation errors.
>
>I need to remove the , when is the firt character.
>I tried using ltrim but it does nothing.
Hi TG, LTRIM is for removing leading spaces only. Try this: SELECT CASE WHEN LEFT(error_code, 1) <> ',' THEN error_code ELSE STUFF(error_code, 1, 1, '') END FROM (SELECT ',C101,C102' AS error_code UNION SELECT 'C100,C101,C103') AS der; Or, if you prefer sneaky, unmaintainable, but short code, try this: SELECT STUFF(error_code, 1, CHARINDEX(',', LEFT(error_code, 1)), '') FROM (SELECT ',C101,C102' AS error_code UNION SELECT 'C100,C101,C103') AS der; --
[quoted text, click to view] On Wed, 26 Jul 2006 08:06:15 +0300, Uri Dimant wrote:
>TG
>Take a look at REPLACE function in the BOL
Hi Uri, REPLACE can't be used here - that would remove all comma's from the string instead of just the leading comma. Unless you had something in your mind such as SELECT REPLACE(LTRIM(REPLACE(error_code, ',', ' ')), ' ', ',') FROM (SELECT ',C101,C102' AS error_code UNION SELECT 'C100,C101,C103') AS der; --
Don't see what you're looking for? Try a search.
|
