Re: Trim Help

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Mike Labosh (mlabosh_at_hotmail.com)
Date: 06/17/04 

Date: Thu, 17 Jun 2004 15:56:37 -0400

> I have some junk data in a column. It is supposed to be a
> a phone number column, but someone before me had made it
> a 25 char field. I noticed I have a few entries in the
> field like this
>
> -233-3457
>
> When it should be just this:
>
> 2333457
>
> Is there a way to trim the 5th character ('-')?

You would not believe how much of this I have done. I solved it by using a
regular expression on the front end inputs to lift off all characters that
are not digits.

If you wanted to handle this cleaning inside the SQL Server you could do
something similar using sp_OACreate, spOASetProperty, spOAGetProperty,
sp_OAMethod & sp_OADestroy to manipulate a "VBScript.RegExp" COM object.

I've heard that the latter takes a performance hit ont he server, though I
haven't tried it.

One thing that you will need to consider also is numeric prefixes. Each
country has a trunk prefix which is dialed just before the area code for a
domestic call (that's a 1 in the US & Canada). For an international call, a
phone number begins with the International Prefix of the country of origin,
followed by the country code of the destination, then the area code and
number. If that stuff is important to you, you will need to check it and
verify it or not chop it off by accident or something. Here's a great
reference:

http://www.wtng.info

Then there's the bit about alpha characters. If you get a phone number like
"1-800-CALL-NOW" you will need to decide whether to disallow the alpha
characters, or convert them to the appropriate digits with a function.

But then what about "1-800-555-1234 ext 4700" ? You wouldn't want to
convert the "ext" into '398'.

You may be in for lots of meetings and policy discussions at work. I don't
envy your task. I've already been through it here.

Peace & happy computing,

Mike Labosh, MCSD
"SELECT * FROM Users WHERE Clue > 0"

Relevant Pages

  • Re: A Question About Dial 1 in USA Calling
    ... number or can you dial just area code + number? ... The 1 prefix in the NANPA countries is equivalent to the 0 prefix in most ... Followed by 1, a country ...
    (comp.dcom.telecom)
  • Re: Creating a sequence number
    ... This number could be concatenated as needed with the prefix. ... (leaving out the two-digit year that occupies the leftmost two characters) ... My thinking here is that since you will store a primary key, ... the same comment applies to the daily sequence number. ...
    (microsoft.public.access.forms)
  • Re: [PHP] global address collection
    ... plus sign to indicate that the phone number starts with the country prefix. ... You can thing of local phone numbers as your current directory. ... There is no mandatory format to separate international dialing prefix, ...
    (php.general)
  • RE: Can a Text Prefix be De-Concatenated??
    ... I will stick to a 5 digit prefix ... characters may involve "4 or 5" characters. ... barcode can be done with code like the untested code below: ... 'open form and locate record ...
    (microsoft.public.access.formscoding)
  • RE: Can a Text Prefix be De-Concatenated??
    ... I will stick to a 5 digit prefix ... characters may involve "4 or 5" characters. ... barcode can be done with code like the untested code below: ... 'open form and locate record ...
    (microsoft.public.access.formscoding)