October 11, 2009 at 4:29 pm
I am attempting to removing parentheses and hyphens from phone numbers data I received from a flat file. I am hoping to removing them then put them in a standard format. Here is an example of same of the data
CREATE TABLE #TEMP (fullname varchar (36), phone varchar (20))
INSERT INTO #temp (FullName, Phone) VALUES ('Tommy', '9735551212')
INSERT INTO #temp (FullName, Phone) VALUES ('Joey','201123-4456')
INSERT INTO #temp (FullName, Phone) VALUES ('Michael','7674356657')
INSERT INTO #temp (FullName, Phone) VALUES ('Vincent','345667-5677')
INSERT INTO #temp (FullName, Phone) VALUES ('Pasquale','6767663535')
INSERT INTO #temp (FullName, Phone) VALUES ('Anthony','356764-6878')
INSERT INTO #temp (FullName, Phone) VALUES ('Paulie','4524523454')
INSERT INTO #temp (FullName, Phone) VALUES ('Maria','234523-4523')
INSERT INTO #temp (FullName, Phone) VALUES ('Stephanie','(123)4542356')
INSERT INTO #temp (FullName, Phone) VALUES ('Helen','(245)234-5234')
INSERT INTO #temp (FullName, Phone) VALUES ('Antoinette','245234-5455')
INSERT INTO #temp (FullName, Phone) VALUES ('Paula','(452)356-2565')
I can put them into three different columns, but the goal is to remove the unwanted characters then add them in a standard format (i.e. (xxx) xxx-xxxx)
I am trying to do it in a single SELECT statement. Am I asking too much?
Thanks everyone!
-tjm
October 11, 2009 at 6:54 pm
How about this:
;WITH cteWithId(id, fullname, phone) AS (
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
FullName,
Phone
FROM #temp
), cteChars(id, ch) AS (
SELECT
W.id,
SUBSTRING(W.phone, N.number, 1)
FROM cteWithId W
JOIN master.dbo.spt_values N
ON (N.type = 'P' AND N.number BETWEEN 1 AND 20)
WHERE (SUBSTRING(W.phone, N.number, 1) BETWEEN '0' AND '9')
), cteLength(id, length) AS (
SELECT
id,
COUNT(*)
FROM cteChars
GROUP BY id
)
SELECT
W.fullname,
W.phone,
CASE L.length
WHEN 10 THEN
STUFF(
STUFF(
STUFF(
(SELECT ch + '' FROM cteChars WHERE id = W.id FOR XML PATH(''))
, 1, 0, '('
), 5, 0, ') '
), 10, 0, '-'
)
ELSE '(invalid)' END AS phone_formatted
FROM cteWithId W
INNER JOIN cteLength L ON (W.id = L.id)
THE cteWithId CTE adds a synthetic unique column using ROW_NUMBER() function. if your actual data has a unique (primary) key, then this CTE is unecessary.
The cteChars CTE splits the phone column into individual characters and filters out any characters that are not numeric digits. This step uses a Numbers (aka Tally) table, in this case the built-in table master.dbo.spt_values.
The cteLength CTE determines the number of numeric digits in each phone number.
The final SELECT statement uses the FOR XML PATH clause to concatenate the numeric digits, and the STUFF function to insert the '(', ') ' and '-' characters, to produce the required format '(XXX) XXX-XXXX'. In the query above, any phone numbers that do not have exactly 10 numeric digits will have the phone_formatted column set to the string '(invalid)', and although it's likely that this is not what you want, it should be straightforward to amend the query to suit your requirements.
EDIT: changed UPDATE to SELECT statement to match OP's requirements
October 11, 2009 at 7:08 pm
Let me review. I'll get back to you in the morning.
Thank you!
-tjm
October 12, 2009 at 7:58 am
Maybe all you need is a replace for each character.
UPDATE #temp SET Phone = REPLACE(Phone, '(', '')
UPDATE #temp SET Phone = REPLACE(Phone, ')', '')
UPDATE #temp SET Phone = REPLACE(Phone, '-', '')
Then update with standard format.
UPDATE #temp SET Phone = '(' + SUBSTRING(Phone,1,3) + ') ' + SUBSTRING(Phone,4,3) + '-' + SUBSTRING(Phone,7,4)
Steve
October 12, 2009 at 8:27 am
This will work although you might want to do it in more then one pass:
SELECT
fullname,
STUFF(
STUFF(
STUFF(
REPLACE(REPLACE(REPLACE(phone, '(', ''), ')', ''), '-', ''),
1,
0,
'('
),
5,
0,
')'
),
9,
0,
'-'
) AS phone
FROM
#temp
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 12, 2009 at 8:56 am
Jack,
That is exactly what I needed! You are the man!
October 12, 2009 at 9:03 am
Thanks, and you are welcome. Be sure you understand how it works before you implement it though. You need to be able to maintain it. I'm also sure it can be improved upon.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 12, 2009 at 2:44 pm
Jack,
Do you know where I can find any SQL server (Stored Procedure, functions, query, etc) labs I can work on to better my development? Any recommendations or advice to speed up the learning process?
Thank you!
-tjm
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply