Writing a SQL query to ignore non numeric characters

I found that the data in the table that I am querying is not standard.  The phone number fields are text fields and have phone numbers in several different formats:  [xxx-xxx-xxxx]  or [xxx/xxx-xxxx] or [xxx xxx xxxx] or [(xxx)xxx-xxxx].  My client cannot standardize the data and wants me to write a query that will ignore non-numeric characters.  I will be looking to match a phone number in xxxxxxxxxx format.  How can i structure the where clause to ignore anything non-numeric?
juliemcnicholsAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
If what you mean is, "I want 1234567890 to match even if the entry is 123-456-7890, (123) 456-7890, etc." then:


SELECT *
FROM SomeTable
WHERE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(PhoneNo, '(', ''), ')', ''), '-', ''), '.', ''), ' ', ''), '/', '') = '1234567890'

Open in new window

0
 
Om PrakashCommented:
--Returns only numeric data
SELECT * FROM YOUR_TABLE WHERE ISNUMERIC(your_phone_field) = 1
0
 
ashishgamre11Commented:
I think following will work:

select replace(phone, substring(phone, patindex('%[^a-zA-Z0-9 ]%', phone), 1), '') as phone from       table_name
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
ashishgamre11Commented:
In the query, replace 'phone' with <your phone number field>
and 'table_name' with <your table name>.
0
 
ashishgamre11Commented:
IF YOU WANT TO UPDATE THE TABLE then, the following query might be helpful:

select * from <your_table_name>
      while @@rowcount > 0
            update       <your_table_name>
            set      <your_phone_number_field> = replace(<your_phone_number_field>, substring(<your_phone_number_field>, patindex('%[^a-zA-Z0-9 ]%', <your_phone_number_field>), 1), '')
            where      patindex('%[^a-zA-Z0-9 ]%', <your_phone_number_field>) <> 0

select * from <your_table_name>


THE QUERY WORKS IN MS SQL SERVER 2005. I THINK IT WILL ALSO WORK IN MS SQL SERVER 2008
0
 
LowfatspreadCommented:
is this a one off query?
how many rows in the table?
is this to be the prime selection criteria?
0
 
cyberkiwiConnect With a Mentor Commented:
Hi,
This will give you the result you are after I believe
CREATE FUNCTION ExtractPhone(@String VARCHAR(2000))
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @Count INT
DECLARE @IntNumbers VARCHAR(1000)
SET @Count = 0
SET @IntNumbers = ''

WHILE @Count <= LEN(@String)
BEGIN
IF SUBSTRING(@String,@Count,1) >= '0'
AND SUBSTRING(@String,@Count,1) <= '9'
BEGIN
SET @IntNumbers = @IntNumbers + SUBSTRING(@String,@Count,1)
END
SET @Count = @Count + 1
END

RETURN @IntNumbers
END
GO

declare @tmp table (Phone varchar(100))
insert @tmp select 'john,doe123'
insert @tmp select 'jane,12vd21100-1doe'
insert @tmp select 'prince'
insert @tmp select '123-212-133'
insert @tmp select '1321546545'
select dbo.ExtractPhone(Phone)
from @tmp

-------
Result
-------
123
12211001

123212133
1321546545

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.