Transact SQL StripNonNumeric Function

Posted in Development, Transact SQL | Wednesday, December 10th, 2008 | No Comments » Trackback

I was looking to scrub some phone fields that allow for some dirty varchar values at the moment, so I needed a tool to strip non-numeric values from the existing data to determine which records had legit phone numbers and which did not.  Here is a quick function I wrote to help me with this in case anyone needed something similar. I included both the StripNonNumeric function and a more generic StripPattern function that you can use to basically do the same thing, but strip any pattern you like.

PLAIN TEXT
SQL:
  1. CREATE CREATE dbo.StripNonNumeric(@value AS VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS
  2. BEGIN
  3.     DECLARE @len AS INT
  4.     DECLARE @pattern AS VARCHAR(5)
  5.     DECLARE @result AS VARCHAR(MAX)
  6.    
  7.     SET @len = LEN(@Value)
  8.     SET @pattern = '[0-9]'
  9.     SET @result = ''
  10.    
  11.     WHILE @len> 0
  12.     BEGIN
  13.         SET @result = @result + CASE WHEN SUBSTRING(@value,@len,1) LIKE @pattern THEN SUBSTRING(@value,@len,1) ELSE '' END
  14.         SET @len = @len - 1
  15.     END
  16.     RETURN reverse(@result)
  17. END
  18.  
  19.  
  20. CREATE FUNCTION dbo.StripPattern(@value AS VARCHAR(MAX), @pattern AS VARCHAR(100)) RETURNS VARCHAR(MAX) AS
  21. BEGIN
  22.     DECLARE @len AS INT
  23.     DECLARE @result AS VARCHAR(MAX)
  24.    
  25.     SET @len = LEN(@Value)
  26.     SET @result = ''
  27.    
  28.     WHILE @len> 0
  29.     BEGIN
  30.         SET @result = @result + CASE WHEN SUBSTRING(@value,@len,1) LIKE @pattern THEN SUBSTRING(@value,@len,1) ELSE '' END
  31.         SET @len = @len - 1
  32.     END
  33.     RETURN reverse(@result)
  34. END

Leave a Reply

Installed by SimpleScripts