Posted by Erland Sommarskog on 10/02/66 11:43

(ILCSP@NETZERO.NET) writes:
> HomePhone:
>
IIf(IsNull([HomePhone]),"0000000000",IIf(Left([HomePhone],1)="(",Right(Left(
[Homephone],4),3)
> & Right(Left([Homephone],9),3) & Right([HomePhone],4),[HomePhone]))
>

CASE WHEN HomePhone IS NULL
THEN '0000000000'
WHEN substring(HomePhone, 1, 1) = '('
THEN substring(HomePhone, 2, 3) + substring(HomePhone, 6, 3) +
substring(HomePhone, 10, 4)
ELSE HomePhone
END

I don't know exactly what the Left and Right functions do, so I had
to make a guess for substring.

A better approact is probably

replace(replace(replace(HomePhone, '(', ''), ')', ''), '-', '')



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: SQL Server 2005: Collation Conflict Error when selecting Database Properties
• Prev in forum: Re: Cursor looping versus set-based queries
• Thread view: Re: Stripping input mask from phone numbers

[Reply to this message]