472,958 Members | 2,404 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,958 software developers and data experts.

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:

& Right(Left([Homephone],9),3) & Right([HomePhone],4),[HomePhone]))

Thanks for all your help.


Mar 28 '06 #1
2 7480
(IL***@NETZERO.NET) writes:
[Homephone],4),3) & Right(Left([Homephone],9),3) & Right([HomePhone],4),[HomePhone]))

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

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
Books Online for SQL Server 2000 at
Mar 28 '06 #2
Thanks Er. That was exactly what I was looking for. Both of them work

Mar 28 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: johnp | last post by:
Hi, Our Tech department updated users to Office 2003 this week. Now the input mask in one of the applications is showing up as: (###) ###-### The input mask wizard works correctly when I...
by: F. Michael Miller | last post by:
I have a db with Access front end, sql back, linked tables. I need to be able to change input masks at the table level in code. Any ideas? Thanks!
by: AA Arens | last post by:
When I want the first character of a field to be Uppercased, I need to make an input mask, like >L< followed by ??????? for example. But this mask creates ____ in an unfilled field, which I don't...
by: RD | last post by:
We always have to have an area code but user does not always have to dial the long distance digit 1 before the area code. When user does not have to make long distance call the phone number our...
by: Matt | last post by:
I recently came across what I believe to be a peculiar bug with Mdi Children, and wanted to see if anyone else had experienced this before sending it in. The issue is as follows: I have a number...
by: =?Utf-8?B?RG9u?= | last post by:
Hello, I have web form that references a field for a phone number. The number is stored in the table as a string in this format "(111) 222-3333". When I done this in the past for window forms I...
by: louie310 | last post by:
I finally came up with an answer for creating a custom message if the input mask is violated. In the properties window for the phone field, open the data tab. In the validation rule I typed in the...
by: desklamp | last post by:
I'm a total Access newbie, please bear with me! Using Win2K/Access 2003. I'm trying to create a table in which I can store IP addresses and other information. According to Microsoft, there is no...
by: phpmel | last post by:
Hi all, I wanted to find out something. If you have an input box on an html form and you wanted it to operate as follows. It will be used to enter a phone number with no dashes. The phone number...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.