Comma Seperators

• sharonsql2013

  SSCertifiable

  Points: 5908

  More actions

  April 14, 2014 at 10:06 am

  #396600

  I am trying to remove "," from strings .( Only if there is no string after the comma)

  example :

  abc ,

  cde,xyz,aaa,

  zzz, yyy ,ttt,

  I am expecting the output as

  abc

  cde,xyz,aaa

  zzz,yyy,ttt

  If I use Replace:

  Select

  Replace(ApplicationType ,',','')Application

  Then it simply removes all the ",". I believe there should be a case .. Any ideas?

• sharonsql2013

  SSCertifiable

  Points: 5908

  More actions

  April 14, 2014 at 10:22 am

  #1705828

  This resolved the Issue

  case

  when right(rtrim(ApplicationType ),1) = ',' then substring(rtrim(ApplicationType ),1,len(rtrim(ApplicationType ))-1)

  Else ApplicationType

  End as Application

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 14, 2014 at 10:32 am

  #1705835

  This will work as well:

  with SampleData as (

  select

  StrVal

  from

  (values ('abc ,'),('cde,xyz,aaa,'),('zzz, yyy ,ttt,'),('qwe'),('asd,fgh'))dt(StrVal)

  )

  select

  StrVal,

  left(StrVal,len(StrVal) - patindex(',%',reverse(StrVal)))

  from

  SampleData;