Need to Strip non-numerics from phone field

I tried searching forums for the last hour and could not find much.

Rather then doing...

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RE PLACE(ISNULL(PHONE, ''), ' ', ''), '(', ''), ')', ''), '-', ''), '.', ''), ':', ''), '+', '')

I was hoping there was an equally efficient alternative that utilizes PATINDEX('%[^0-9]%', PHONE)

to some capacity (i.e. uses regular expressions to strip all non-numeric characters from the phone field)

I am certain this has been addressed before, just not sure if the "REPLACE to the nth degree" is the only solution.

THANKS!Any way to make this recursive based on the actual length in the field? Or would I take a performance hit?

SELECT
CASE WHEN SUBSTRING( PHONE , 1 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE , 1 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE , 2 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE , 2 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE , 3 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE , 3 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE , 4 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE , 4 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE , 5 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE , 5 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE , 6 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE , 6 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE , 7 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE , 7 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE , 8 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE , 8 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE , 9 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE , 9 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,10 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,10 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,11 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,11 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,12 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,12 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,13 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,13 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,14 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,14 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,15 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,15 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,16 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,16 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,17 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,17 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,18 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,18 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,19 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,19 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,20 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,20 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,21 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,21 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,22 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,22 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,23 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,23 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,24 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,24 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,25 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,25 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,26 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,26 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,27 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,27 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,28 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,28 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,29 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,29 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,30 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,30 , 1 ) ELSE '' END
AS NUMERICVALUEONLY
FROM COUNCIL_MEMBER|||I'd use one of my old handy-dandys that I wrote for just this kind of job!CREATE FUNCTION dbo.fNumeric(@.pcIn VARCHAR(100)) RETURNS VARCHAR(100) AS
BEGIN
DECLARE @.i INT

SET @.i = PatIndex('%[^0-9]%', @.pcIn)

WHILE 0 < @.i
BEGIN
SET @.pcIn = Stuff(@.pcIn, @.i, 1, '')
SET @.i = PatIndex('%[^0-9]%', @.pcIn)
END

RETURN @.pcIn
END
GO-PatP|||I'd use one of my old handy-dandys that I wrote for just this kind of job!CREATE FUNCTION dbo.fNumeric(@.pcIn VARCHAR(100)) RETURNS VARCHAR(100) AS
BEGIN
DECLARE @.i INT

SET @.i = PatIndex('%[^0-9]%', @.pcIn)

WHILE 0 < @.i
BEGIN
SET @.pcIn = Stuff(@.pcIn, @.i, 1, '')
SET @.i = PatIndex('%[^0-9]%', @.pcIn)
END

RETURN @.pcIn
END
GO-PatP

While it looks pretty, it doesn't look very efficient - it does give me an idea though, I'll post back if I have a more elegant solution then my last post.|||Something like this might work if I packaged it into a UDF, not sure how much of a performance hit I would take with the While loop (I know very similar to what Pat posted - ok nearly identical) :)

DECLARE @.phone VARCHAR(100)
SELECT @.phone = '123ab 34 902x:a123ab 34 902x:a'

WHILE (PATINDEX('%[^0-9]%', @.phone) > 0)
SELECT @.phone = REPLACE(@.phone, SUBSTRING(@.phone, PATINDEX('%[^0-9]%', @.phone), 1), '')

SELECT @.phone|||The only difference is he gets Estimated Row Size of 61 while yours holds at 4 regardless of the string length and contents.|||The only difference is he gets Estimated Row Size of 61 while yours holds at 4 regardless of the string length and contents.I'm not sure that I understand the significance of that comment, but Ok. The PatIndex() call is (or at least was when I wrote this) expensive in terms of both time and resources consumed, so I was trying to minimize those calls.

-PatP|||Nothing significant, Pat, I was just trying to identify the differences, that's all ;)