468,765 Members | 916 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,765 developers. It's quick & easy.

Stripping input mask from phone numbers

Hello, I have this Access 2K query that I need to re-create in MS SQL
Server 2000, so I'm using the Query Analyzer to test it.

One of the Access fields stores the home phone number. In the Access
query, if the phone number is null, it fills it up with zeroes
"000000000." If the phone has an input mask, it only gets the 9 numbers
(area code included) and if the phone number's good (all numbers) then
it leaves it alone. That Access query is using immediate ifs to
accomplish that task.

Does anyone have any idea how to copy this behavior into SQL Server
2000? I've using the CASE statement but so far my code is not correct.
I get stuck in the input mask. This is the Access code:

HomePhone:
IIf(IsNull([HomePhone]),"0000000000",IIf(Left([HomePhone],1)="(",Right(Left([Homephone],4),3)
& Right(Left([Homephone],9),3) & Right([HomePhone],4),[HomePhone]))

Thanks for all your help.

JR.

Mar 28 '06 #1
2 7245
(IL***@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 28 '06 #2
Thanks Er. That was exactly what I was looking for. Both of them work
beautifully.

Mar 28 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by johnp | last post: by
7 posts views Thread by F. Michael Miller | last post: by
2 posts views Thread by =?Utf-8?B?RG9u?= | last post: by
6 posts views Thread by phpmel | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by Marin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.