Discard non-numeric characters from a string

Question

I am currently doing a data conversion project and you need to remove all alphabetical characters from the string. Unfortunately, I cannot create or use a function, since we don’t own the original machine, because of which the methods that I found when searching for previous messages are unsuitable.

What would be the best way to do this in the select statement? Speed ​​is not a big deal, as it will only work on 30,000 records or so, and it’s shutting down.

Answer 1

RichardTheKiwi script in the function to use when selecting without applying the cross, a dot is also added, because in my case I use it for double and monetary values ​​in the 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

Answer 2

You can do this in one application. You really don't create an expression with 200 + REPLACE, 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) 

SQL Fiddle showing this query with sample data

Replication 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 

Answer 3

CTE for HELP is 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 

Answer 4

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

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

Obviously, that would be a lot uglier than that, since I only processed the first three letters, but that should have given an idea.