Posted by ILCSP on 10/02/42 11:43

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.

• Next in forum: Re: Odd query plan for view
• Prev in forum: Re: Cursor looping versus set-based queries
• Thread view: Stripping input mask from phone numbers

[Reply to this message]