June 6, 2005 at 7:39 am
Good morning to all. A little help with the following. I am migrqatiing data from a DB2 system onto SQL Server 2000. Everything was going fine untul I got to the phone numbers. Some have the area code included some, quite a large number of records, without it.
I used somekind of statement whit LEN to identify those records without area code and enter a blank space in the area code field. Please see below
SELECT LEN(CLPHN) AS PhoneLength,
CASE CLPHN
WHEN LEN(CLPHN)>7 THEN substring(CLPHN,1,3)
WHEN LEN(CLPHN)<=7 THEN '' END ELSE AS ClientAreaCode,
rtrim(ltrim(CLPHN)) AS ClientPhoneNumber,
CLPHN
FROM CLIENTPF
ORDER BY CLNUMBER
The stetement returns an error message stating the following:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '>'.
I used this expression before, but today I am too cold, I csn't even think how did I get records without any problems. Any suggestion? HELP!! Thank you
June 6, 2005 at 8:03 am
Are you looking for this?
SELECT
LEN(CLPHN) AS PhoneLength
, CASE
WHEN LEN(CLPHN)>7 THEN substring(CLPHN,1,3)
ELSE ''
END AS ClientAreaCode
, rtrim(ltrim(CLPHN)) AS ClientPhoneNumber
, CLPHN
FROM CLIENTPF
ORDER BY CLNUMBER
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 6, 2005 at 8:13 am
Thanks Frank, it was almost what I needed, but with your sample I modified a couple of things and it work perfectly. Thanks again
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply