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 · Accepted Answer · 2011-09-23 06:15:07Z

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, 2011 at 6:15

answered Sep 23, 2011 at 5:04

Igor Borisenko

3,8663 gold badges38 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rusty Fausak · Accepted Answer · 2011-09-23 04:27:27Z

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, 2011 at 4:27

answered Sep 23, 2011 at 4:05

Rusty Fausak

7,5541 gold badge30 silver badges39 bronze badges

4 Comments

Ishwor Over a year ago

Can I do something without having to "UPDATE" my DB?

Rusty Fausak Over a year ago

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.

Ishwor Over a year ago

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.

Rusty Fausak Over a year ago

You still haven't specified what you are trying to do. I added a MySQL solution to selecting what you are looking for. See stackoverflow.com/questions/287105/…

Meisam Mulla · Accepted Answer · 2011-09-23 03:56:13Z

0

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

ex. get all rows -> replace -> update

answered Sep 23, 2011 at 3:56

Meisam Mulla

1,8713 gold badges23 silver badges37 bronze badges

Comments

Icarus · Accepted Answer · 2011-09-23 05:20:47Z

0

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')

edited Sep 23, 2011 at 5:20

Icarus

64.1k14 gold badges102 silver badges116 bronze badges

answered Sep 23, 2011 at 4:55

B'man Sresta

1

Comments

John Ken · Accepted Answer · 2011-09-23 06:10:02Z

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, 2011 at 6:10

John Ken

9621 gold badge13 silver badges28 bronze badges

Collectives™ on Stack Overflow

Replace all non numeric characters in MSSQL with an empty string

5 Answers 5

Comments

4 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related