Expression to remove commas in dervied column?

• Charmer

  SSChampion

  Points: 14903

  More actions

  May 7, 2012 at 5:56 am

  #149206

  Hi Friends,

  I want to remove comma which is occurred to the end of the string...

  my sample data is like below

  1.MPED,MWWA,MEMS

  2.GAPD,

  i want the above the result like

  1.MPED,MWWA,MEMS

  2.GAPD

  so what is the expression to be used in the derived column?

  Thanks,
  Charmer

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  May 7, 2012 at 7:14 am

  #1484055

  Have you tried?

  DECLARE @I VARCHAR(20)

  SET @I = 'GAPD,'

  IF CHARINDEX(',',REVERSE(@I),1) = 1

  BEGIN

  SELECT REVERSE(SUBSTRING(REVERSE(@I),2,DATALENGTH(@I)))

  END

  ELSE

  SELECT @I

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• robert.gerald.taylor

  SSChampion

  Points: 13133

  More actions

  May 7, 2012 at 7:17 am

  #1484057

  In a Derived column transform, you can do something like:

  [font="Courier New"]RIGHT(Field1,1) == "," ? SUBSTRING(Field1,1,LEN(Field1) - 1) : Field1[/font]

  I didn't check for an empty string or other possible problems, but this should give you the main idea that you can flesh out.

  HTH,

  Rob

• Charmer

  SSChampion

  Points: 14903

  More actions

  May 7, 2012 at 7:24 am

  #1484060

  robert.gerald.taylor (5/7/2012)

  ---

  In a Derived column transform, you can do something like:

  [font="Courier New"]RIGHT(Field1,1) == "," ? SUBSTRING(Field1,1,LEN(Field1) - 1) : Field1[/font]

  I didn't check for an empty string or other possible problems, but this should give you the main idea that you can flesh out.

  HTH,

  Rob

  I already triggered out the solution...the same one that you suggested above...

  Thank you buddy

  Thanks,
  Charmer