View Single Post
  #4 (permalink)  
Old June 13th, 2004, 06:32 PM
owain owain is offline
Friend of Wrox
Join Date: Jun 2003
Location: Cardiff, , United Kingdom.
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts

If you are using SQL Server (I don't know about Oracle) then you could use the REPLACE() function. Here is the example I tried:
  FROM tblClients
 WHERE REPLACE(REPLACE(REPLACE([Phone], '-', ''), '(', ''), ')', '')
       LIKE '%5551212%'
       This will replace hyphens, open brackets and close brackets symbols with empty strings. You can remove more symbols by nesting more REPLACE() functions. This could also easily be added to a UDF if required.

If you need to remove all non numeric characters then you will need to loop through the string testing each character. This can be done in a WHILE loop using the SUBSTRING() function. I tried to create a UDF that would do this but I could not get it to work, unfortunately I do not have a great deal of experience with SQL Server so I am probably just making a silly mistake, the code I tried is below, maybe someone with more experience would be kind enough to take a look at it and fix the problems:
CREATE FUNCTION PhoneStrip(@expression varchar(20))
RETURNS varchar(20)
    DECLARE @index int
    DECLARE @return varchar(20)

    @index = 1

    WHILE @index < LEN(@expression)
        IF SUBSTRING(@expression, @index, 1) BETWEEN '0' AND '9'
            @return = @return + SUBSTRING(@expression, @index, 1)
        @index = @index + 1
    RETURN @return
In the mean time the REPLACE() function should suffice. I hope this helps.

Owain Williams
Reply With Quote