Deleting part of a field from column

• //Ravi

  SSCrazy

  Points: 2612

  More actions

  January 4, 2008 at 12:58 pm

  #185343

  hello,

  I am currently working on SSIS.

  I have column "description." The fields in this column have some weird stuff at the end. They end in "-O/P","-I/P","-RCURRING","-RECUR." How can I chop off just the end of the field?

  I have tried using this string but it chops off front of the field, not the end.

  UPDATE [Description] SET [Description] = SUBSTRING([Description], 5, LEN([Description]) - 4) WHERE [Description] LIKE '%-O/P'

  Can someone help?

  Thanks in advance.

  Ravi.

  ------------
  🙂

• antonio.collins

  SSCrazy Eights

  Points: 9600

  More actions

  January 4, 2008 at 4:01 pm

  #764998

  your example drops the first 5 characters of the string, not the last 5.

  i don't think TSQL can easily find the last occurrence of a character. so i suggest you reverse() the string, find the first occurrence of '-', and use that position to slice your field. here's an example which explains the tactic:

  select cast("pink -OP" as varchar(30)) as val into #x

  insert into #x values ("red -RECU" )

  insert into #x values ("blue-green -OK" )

  go

  select val, left( val, len(val) - charindex('-',reverse(val)) )

  from #x

  so your code would become:

  UPDATE [Description]

  SET [Description] = LEFT([Description], LEN([Description]) - CHARINDEX('-',REVERSE([Description])))

  WHERE [Description] LIKE '%-O/P' OR [Description] LIKE '%-RECUR' OR ...

  be sure to only update the rows with the errant suffixes.