Simple Phone Number Formatting

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Simple Phone Number Formatting

Author

Topic

bobbles22
Starting Member

18 Posts

Posted - 2010-08-20 : 04:19:33

Hi Guys,

I'm sure this must be fairly simple. I have a table with three columns, countrycode, areacode and phonenumber. Currently, most phone numbers have been entered in their entirity within just the phonenumber column, with the country and area code columns left as NULL.

I want to run a query that looks for the first few digits of the phonenumber column and if they match a pattern, enter that pattern in the area code field and delete it from the phonenumber field. EG if it sees 01442123456 and it is looking for 01442, it enters 01442 in the area code field and deletes it from the beginning of the phonenumber field, leaving only 123456.

If I can do this, I can easily amend it to do the same for the country codes.

I hope that makes sense.

Many thanks to anyone who can help.

Bob

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-20 : 05:02:55

Does the phone code have always 11 digits?

Madhivanan

Failing to plan is Planning to fail

bobbles22
Starting Member

18 Posts

Posted - 2010-08-20 : 05:20:10

No, they are very randomly formatted. Some have just the number eg 123456, some have the area code and number eg 01442123456 or 01442 123456 or 1442123456, and some include the country code too eg 004401442123456 or +44 01442 123456, so the formatting is all over the place.

Bob

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-20 : 05:28:31

Ok. Can you post some more sample data with expected result?

Madhivanan

Failing to plan is Planning to fail

bobbles22
Starting Member

18 Posts

Posted - 2010-08-20 : 06:01:03

I want to get from:

areacode=NULL
phonenumber=01442123456

to

areacode=01442
phonenumber=123456

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-20 : 06:12:50

quote:
Originally posted by bobbles22

I want to get from:

areacode=NULL
phonenumber=01442123456

to

areacode=01442
phonenumber=123456

Post some more sample data with expected result as the length is varying

Madhivanan

Failing to plan is Planning to fail

bobbles22
Starting Member

18 Posts

Posted - 2010-08-20 : 06:27:48

Where it is currently formatted

areacode phonenumber
NULL 01442123456
NULL 01442 123456
NULL 1442123456
NULL +1442123456

All would need to end up like

01442 123456

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-20 : 06:48:14

Try this

First run this select to see if it gives you correct result

select replace(left(phonenumber,len(phonenumber)-6),'+',0') as areacode, right(phonenumber,6) as phonenumber from your_table

If it gives you correct result, run this update

update your_table
set areacode=replace(left(phonenumber,len(phonenumber)-6),'+',0') ,
phonenumber=right(phonenumber,6)
from your_table

Madhivanan

Failing to plan is Planning to fail

bobbles22
Starting Member

18 Posts

Posted - 2010-08-20 : 09:51:30

I've modified it slightly:

update dbo.phone
set phon_areacode=replace(left(phon_number,len(phon_number)-6),'+','0') ,
phon_number=right(phon_number,6)
from dbo.phone
where phon_number not like '0121%'
or '0131%'
or '0141%'
or '0151%'
or '0161%'
or '0171%'
or '0181%'
or '0191%'
or '0207%'
or '0208%'
or '0845%'
or '0844%'
or '0870%'
or '00%'
or '+%'
or '/%'
or '1%'
or '2%'
or '3%'
or '4%'
or '5%'
or '6%'
or '7%'
or '8%'
or '9%'
or ' %'

However when I run it, I get the following error:

Msg 4145, Level 15, State 1, Line 7
An expression of non-boolean type specified in a context where a condition is expected, near 'or'.

I'm not quite sure what I've done wrong here.

Any ideas?

Thanks for all your help on this.

Bob

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-20 : 10:12:32

You need to include phon_number not like for all the numbers

Madhivanan

Failing to plan is Planning to fail

bobbles22
Starting Member

18 Posts

Posted - 2010-08-20 : 10:27:35

Now recieving this error:

Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.
The statement has been terminated.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-20 : 11:10:57

You have phon_number's that are too short to manipulate using LEFT - 6

(I.e) there are strings in there less than 6 characters long.

you can get around this using CASE.

The best thing you can do is give us a sample table containing one example of each permutation of phon_number you've found so far and explain exactly what results you want from that sample.

Regards,

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

bobbles22
Starting Member

18 Posts

Posted - 2010-08-23 : 05:58:38

Thanks for everyones help on this so far.

Suppose I wanted to move all 11 digit numbers in my phonenumber field, just 11 digits in length, none others.

If I wanted to take the first 5 only and move them to the areacode field, what would be the query to do that?

So:

Areacode Phonenumber
NULL 01234567890

becomes

01234 567890

This is obviously a simple version of what has been disucssed before. I guess this would use a LEN command?

Many thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-23 : 06:32:20

For the simple case of exactly 11 characters in PhoneNumber something like


SELECT
    LEFT([phoneNumber], 5)
    , RIGHT([phoneNumber], 6)
FROM
    <TheTable>
WHERE
    LEN([phoneNumber]) = 11

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-23 : 06:33:41

OR if you wanted to update


UPDATE t
SET
    [AreaCode] = LEFT([phoneNumber], 5)
    , [phoneNumber] = RIGHT([phoneNumber], 6)
FROM
    <theTable> AS t
WHERE
    LEN(t.[phoneNumber]) = 11

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

bobbles22
Starting Member

18 Posts

Posted - 2010-08-23 : 07:33:53

Thanks Charlie

When I enter this...

UPDATE dbo.phone
SET
[phon_areacode] = LEFT([phon_number], 5)
, [phon_number] = RIGHT[phon_number], 6)
WHERE
LEN([phon_number]) = 11

I get the error...

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'phon_number'.

Those are the right field names (phon_number, phon_areacode).

Any idea whats wrong on line 4?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-23 : 07:53:46

missing bracket (left parenthesis)


UPDATE dbo.phone
SET
[phon_areacode] = LEFT([phon_number], 5)
, [phon_number] = RIGHT([phon_number], 6)
WHERE
LEN([phon_number]) = 11

Regards,

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

bobbles22
Starting Member

18 Posts

Posted - 2010-08-23 : 08:05:41

Right, the query now runs, but if I start with

PHON_AREACODE PHON_NUMBER
NULL 01442123456

I end up with

PHON_AREACODE PHON_NUMBER
01442 <11 blank spaces>

The last 6 digits have disappeared and the phon_number field is just a series of spaces.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-23 : 10:25:36

what is the datatype of the column? and what is the exact string inside

I think it might be a fixed wide CHAR field (CHAR(50)) or similar.

LEN() trims a string so that:


DECLARE @foo VARCHAR(50)
SET @foo = 'abc         '
SELECT LEN(@foo)

would return 3

you could try this:


UPDATE dbo.phone
SET
[phon_areacode] = LEFT([phon_number], 5)
, [phon_number] = RIGHT(RTRIM([phon_number]), 6)
WHERE
LEN([phon_number]) = 11

The data in this table sounds like it is in a horrible mess.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

bobbles22
Starting Member

18 Posts

Posted - 2010-08-23 : 11:18:27

Mate, the data is so dirty a pig would happily wallow in it.

Its two years worth of guys entering into a Sage CRM with no restrictions as to the format of data going in, plus a few of them fail to engage their brains when entering the data in the first place

Bob

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-24 : 05:52:28

Well you could try this.

1) make a staging table -- only columns would be:

[phoneDetailsReference] -- (A foreign key back to dbo.phone)
, [phon_number] VARCHAR(8000) -- You don't have more than 8000 character phon_numbers do you???
, [phon_length] INT
, [containseInvalidChar] BIT

-- This staging table can be a temp table if you like

2) Populate the staging table. Using something like my example below. I've used a table variable but you'd use a temp table or a base table so you can manipulate the data afterwards.




DECLARE @phone TABLE (
	[personIdentityID] INT IDENTITY(1,1) PRIMARY KEY
	, [phon_number] VARCHAR(50)
	)

INSERT @phone ([phon_number])
      SELECT ''
UNION SELECT '+44 (0)131 270 2223'
UNION SELECT '07789453324   '
UNION SELECT '0141 334 2312'
UNION SELECT '   GLASGOW 456  6664'

SELECT
	[personIdentityID] AS [phoneID]
	, LTRIM(RTRIM(REPLACE([phon_number], ' ', '')))
	, LEN(LTRIM(RTRIM(REPLACE([phon_Number], ' ', ''))))
	, CASE WHEN LTRIM(RTRIM(REPLACE([phon_number], ' ', ''))) LIKE '%[^0123456789]%' THEN 1 ELSE 0 END AS [containsInvalidChar]
FROM
	@phone

3) Then you'd have a staging table where all the phone numbers are only the actual text and you have already a length for each.
Using that then you can look at similar cases (where the phon_number only contains numbers and has set lengths etc.

4) Once manipulated put them back into dbo.phone using the foreign key.

-- Good luck -- even with all this you'll have to deal with a lot of cases manually.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources