String Manipulation in SQL Statement

[RemingtonSteel]

I am doing a data cleansing and most likely I will be using an update statement.


Tel_Field = 551-9000
Province_Field = Toronto, ON

I want to update a telephone field (with an area code) if the province field is ON.

What is the function I can use for this scenario.

Thanks for your help!
Gene

 

[Mike Lewis]

Gene,

How about this:

UPDATE MyTable ;
  SET Tel_Field = "(999) "  + Tel_Field ;
  WHERE Province_Field = "ON"

where "999" is the new area code.

Or, if old area code is already present:

UPDATE MyTable ;
  SET Tel_Field = STRTRAN(Tel_Field, "888", "999") ;
  WHERE Province_Field = "ON"

where 888 is the old code.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[RemingtonSteel]

Thanks a lot Mike!