Derived Column Expressions

• James Summerlin

  SSC Enthusiast

  Points: 143

  More actions

  May 6, 2008 at 11:56 am

  #123717

  Everyone,

  Today's problem is about the derived column transformation.

  I have a column called Phone in the data source. It is a text datatype and the source application stores phone numbers in the format:

  (000) 000-0000. So, an example would be (555) 296-1123.

  I need to remove the parenth, the spaces, and the dash so the number would be 5552961123.

  Using the replace function is easy enough -- REPLACE([Phone], "(", "")

  But, I would like to use multiple replace functions in the derive column transform so I don't have to have multiple transforms for one column.

  Can I do something like

  REPLACE([Phone], "(", "") REPLACE([Phone], ")", "") REPLACE([Phone], "-", "") Trim([Phone])

  I obviously can't type it in that way as I've already tried. Is there some syntax I'm missing? I assume there is a separator.

  This way I can have one derived column to replace the original and execute all those functions on one line and do it all in one transform.

  As always, thank you all.

  JamesNT

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 6, 2008 at 12:29 pm

  #811404

  This is done in T-SQL, but the principle is the same:

  declare @phonenum varchar(25)

  set @phonenum = '(555) 555-1212'

  select replace(replace(replace(replace(@phonenum, '(', ''), ')',''), ' ',''),'-','')

  😎

• James Summerlin

  SSC Enthusiast

  Points: 143

  More actions

  May 6, 2008 at 2:55 pm

  #811490

  Thanks Lynn, that worked great!

  What we have that parses correctly is:

  replace(replace(replace(Phone, "-", ""), ")", ""), "(", "")

  So now I have one derived transform instead of 3.

  Thanks again!

  JamesNT