Replace all non numeric characters in MSSQL with an empty string

Question

My current MSSQL table has a "phone" column which is a varchar. Unfortunately, the phone numbers that are already in the database are not in standard format. For example, 888-888-8888 OR 888/888/8888 OR (888)8888888 OR 8888888888.

I want to get all the rows that are equivalent to 88888888, i.e it should match with 888-888-8888, (888)888888 etc.

I have tried using REPLACE() but there are certain rows where entries have other alphabetic characters like "e", "ex", "ext", etc. So I would like to replace all non-numeric characters.

What would be the best way to get the "matching" rows using MSSQL query?

Igor Borisenko · Answer 1 · 2011-09-23T06:15:07.987

2

You can try this function (MS SQL Server):

CREATE FUNCTION uf_RemoveNotNumbers (@str varchar(max))
RETURNS varchar(max)
AS
BEGIN
    WHILE @str LIKE '%[^0-9]%' 
    SET @str=replace(@str, substring(@str, patindex('%[^0-9]%',@str),1),'');
    RETURN @str
END

GO

DECLARE @str varchar(max);
SET @str = 'q56--89+9*67qweresr';
select dbo.uf_RemoveNotNumbers (@str)

edited Sep 23 '11 at 06:15

answered Sep 23 '11 at 05:04

Igor Borisenko

3,806
3
34
49

Rusty Fausak · Accepted Answer · 2011-09-23T04:27:27.433

1

A simple version using MySQL:

SELECT * FROM `phones` WHERE `phone` LIKE '%8%8%8%8%8%8%8%8%8%8%'

Using PHP:

// Get all your table rows into $rows using SELECT ..
foreach ($rows as $row) {
    $row['phone'] = preg_replace('/\D/', '', $row['phone'];
    // Save the row using UPDATE ..
}

The regular expression \D matches any non-numeric character. See php.net/preg_replace for more information.

If you just want to find a row that matches "8888888888", then you could use:

if (preg_match('/\D*8\D*8\D*8\D*8\D*8\D*8\D*8\D*8\D*8\D*8\D*/', $row['phone'])) {
    ..
}

Which could simplify/abstract to:

$match = '8888888888';
if (preg_match('/' . preg_replace('/(\d)/', '\D*$1', $match) . '\D*/', $row['phone'])) {
    ..
}

edited Sep 23 '11 at 04:27

answered Sep 23 '11 at 04:05

Rusty Fausak

7,355
1
27
38

Can I do something without having to "UPDATE" my DB? – Ishwor Sep 23 '11 at 04:09
Well what are you trying to do? Change database rows from "888-888-8888" to "8888888888" or just find a row that matches "8888888888"? I edited my answer to provide a solution to the latter. – Rusty Fausak Sep 23 '11 at 04:12
I wrote "PHP" in my question, that was mistake. I want everything to be done using MSSQL query. I have corrected the question now. Sorry. – Ishwor Sep 23 '11 at 04:20
You still haven't specified what you are trying to do. I added a MySQL solution to selecting what you are looking for. See http://stackoverflow.com/questions/287105/mysql-strip-non-numeric-characters-to-compare – Rusty Fausak Sep 23 '11 at 04:28

score 0 · Answer 3 · answered Sep 23 '11 at 03:56

0

Why not write a php script that would do it for you?

ex. get all rows -> replace -> update

answered Sep 23 '11 at 03:56

Meisam Mulla

1,845
3
23
37

score 0 · Answer 4 · edited Sep 23 '11 at 05:20

heres the query that might work on MSSQL.

create FUNCTION dbo.Only_Numbers

(

    @string VARCHAR(8000)
)

RETURNS VARCHAR(8000)
AS

BEGIN

DECLARE @IncorrectCharLoc SMALLINT
SET @IncorrectCharLoc = PATINDEX('%[^0-9]%', @string)
WHILE @IncorrectCharLoc > 0
BEGIN
    SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')
    SET @IncorrectCharLoc = PATINDEX('%[^0-9]%', @string)
    END
    SET @string = @string
Return  @string 
END

GO

select dbo.Only_Numbers('888*88-88/2')

score 0 · Answer 5 · answered Sep 23 '11 at 06:10

0

You can try this code:

$query="select * from tablename";

$result=mysql_query($query);

while($row=mysql_fetch_array($result))
{
    $str = preg_replace('[\D]', '', $row['phone']);
}

answered Sep 23 '11 at 06:10

John Ken

898
1
13
26

Replace all non numeric characters in MSSQL with an empty string

5 Answers5

Linked