sql server (alternate): Is there a way to filter a string field (nvarchar) for only the
numerics.
Example, I have a column that users use to enter phone numbers.
Sometimes they enter 555-1212, sometimes 5551212, sometimes 555 1212,
sometimes (001)555-1212. (Unfortunately that was the client's
requirement to be able to enter numbers that way. There are no alphas,
but spaces, hyphens and () are permitted.)
What I want to do is to create a stored procedure that allows the user
to search for phone numbers so that if the user searches for "5551212"
or "5121" etc. it will find all the above.
Is this possible?

Example

@NumberSearched varchar(10)

SELECT
c.UniqueID,
c.Name
FROM
dbo.tblClients c
WHERE
SomeMagicCleaningFunction(c.PhoneNumber) LIKE '%' + @NumberSearched +
'%'

Thanks,
lq

Hi

As there are a finite number of characters you want to replace then probably
the easiest way it use:

SELECT
REPLACE(REPLACE(REPLACE(REPLACE(Phone,'-',''),'(',''),')',''),SPACE(1),'')
AS Phone
FROM (SELECT '555-1212' AS Phone
UNION ALL SELECT '5551212'
UNION ALL SELECT '555 1212'
UNION ALL SELECT '(001)555-1212') Tels

If you do this when the data is inserted it may be easier.

And choosing the numbers can be like:

SELECT Phone,
REPLACE(REPLACE(REPLACE(REPLACE(Phone,'-',''),'(',''),')',''),SPACE(1),'')
AS Cleaned_Phone
FROM (SELECT '555-1212' AS Phone
UNION ALL SELECT '5551212'
UNION ALL SELECT '555 1212'
UNION ALL SELECT '(001)555-1212') Tels
WHERE
REPLACE(REPLACE(REPLACE(REPLACE(Phone,'-',''),'(',''),')',''),SPACE(1),'')
like '%5551212%'

John

[quoted text, click to view]

John Bell (jbellnewsposts@hotmail.com) writes:
[quoted text, click to view]

Since users may want to see numbers the way they were entered, it may
not be good to remove the non-digits on inserted. However, you could
add a computed column to the table, and then also index this column
for searches:

CREATE TABLE phnumbers (phoneno varchar(22) NOT NULL,
phoneno_num as convert(varchar(12),
REPLACE(
REPLACE(
REPLACE(
REPLACE(phoneno, '-', ''),
'(', ''),
')', ''),
SPACE(1), ''))
)
go
CREATE INDEX num_ix ON phnumbers(phoneno_num)
go
insert phnumbers(phoneno)
SELECT '555-1212'
UNION ALL SELECT '5551212'
UNION ALL SELECT '555 1212'
UNION ALL SELECT '(001)555-1212'
go
select * from phnumbers
go
drop table phnumbers



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at

Thanks for the ideas. Rather than create a new column to hold redundant
data I used
a variation of:
REPLACE(REPLACE(REPLACE(REPLAC=ADE(Phone,'-',''),'(',''),')',''=AD),SPACE(1=
),'')

like '%' + @NumberSearched + '%' in my query and it works very well.