Strip non-numeric characters from a string

Question

I'm currently doing a data conversion project and need to strip all alphabetical characters from a string. Unfortunately I can't create or use a function as we don't own the source machine making the methods I've found from searching for previous posts unusable.

What would be the best way to do this in a select statement? Speed isn't too much of an issue as this will only be running over 30,000 records or so and is a once off statement.

Solution 1

RichardTheKiwi's script in a function for use in selects without cross apply, also added dot because in my case I use it for double and money values within a varchar field

CREATE FUNCTION dbo.ReplaceNonNumericChars (@string VARCHAR(5000))
RETURNS VARCHAR(1000)
AS 
    BEGIN
        SET @string = REPLACE(@string, ',', '.')
        SET @string = (SELECT   SUBSTRING(@string, v.number, 1)
                       FROM     master..spt_values v
                       WHERE    v.type = 'P'
                                AND v.number BETWEEN 1 AND LEN(@string)
                                AND (SUBSTRING(@string, v.number, 1) LIKE '[0-9]'
                                     OR SUBSTRING(@string, v.number, 1) LIKE '[.]')
                       ORDER BY v.number
                      FOR
                       XML PATH('')
                      )
        RETURN @string
    END
GO

Thanks RichardTheKiwi +1

Solution 2

You can do this in a single statement. You're not really creating a statement with 200+ REPLACEs are you?!

update tbl
set S = U.clean
from tbl
cross apply
(
    select Substring(tbl.S,v.number,1)
    -- this table will cater for strings up to length 2047
    from master..spt_values v
    where v.type='P' and v.number between 1 and len(tbl.S)
    and Substring(tbl.S,v.number,1) like '[0-9]'
    order by v.number
    for xml path ('')
) U(clean)

Working SQL Fiddle showing this query with sample data

Replicated below for posterity:

create table tbl (ID int identity, S varchar(500))
insert tbl select 'asdlfj;390312hr9fasd9uhf012  3or h239ur ' + char(13) + 'asdfasf'
insert tbl select '123'
insert tbl select ''
insert tbl select null
insert tbl select '123 a 124'

Results

ID  S
1   390312990123239
2   123
3   (null)
4   (null)
5   123124

Solution 3

CTE comes for HELP here.

;WITH CTE AS 
(
SELECT 

      [ProductNumber] AS OrigProductNumber
      ,CAST([ProductNumber] AS VARCHAR(100)) AS [ProductNumber]           
FROM [AdventureWorks].[Production].[Product]
UNION ALL
SELECT OrigProductNumber
       ,CAST(STUFF([ProductNumber], PATINDEX('%[^0-9]%', [ProductNumber]), 1, '') AS VARCHAR(100) ) AS [ProductNumber]
FROM CTE WHERE PATINDEX('%[^0-9]%', [ProductNumber]) > 0 
)

SELECT * FROM CTE
WHERE PATINDEX('%[^0-9]%', [ProductNumber]) = 0   
OPTION (MAXRECURSION 0)

output:

OrigProductNumber   ProductNumber
WB-H098                 098
VE-C304-S               304
VE-C304-M               304
VE-C304-L               304
TT-T092                 092

Solution 4

Well if you really can't use a function, I suppose you could do something like this:

SELECT REPLACE(REPLACE(REPLACE(LOWER(col),'a',''),'b',''),'c','')
  FROM dbo.table...

Obviously it would be a lot uglier than that, since I only handled the first three letters, but it should give the idea.

View more solutions

Comments

[Michael A]

Yup... ended up doing this way. Agree it's ugly though

[Adir D]

A T-SQL function wouldn't be all that much prettier, just looks better in the query. Now CLR, on the other hand, would let you do this kind of thing much tidier with RegEx.

[Michael A]

I agree completely - time sensitive project though :)

[Adir D]

+1 more clever than mine but if there are only alphabetic characters that need to be replaced the nested replaces are a little easier to understand. :-)

[RichardTheKiwi]

@Aaron, @Michael I think a more concise TSQL solution is still possible... If exotic characters are in the string or unsure, this answer would have to include REPLACE(..,Char(244),'') etc

[RichardTheKiwi]

You're right. I just had this pattern that I used before for cleaning certain character groups (different WHERE clause), so I thought I'd reuse it.

[Pierre]

+1 for being Awesome! You have the best script by far, tested over 20 scripts by now - All others either not supported by SQL 2005 standards, or Super slow.