Select query to remove non-numeric characters

Question

I've got dirty data in a column with variable alpha length. I just want to strip out anything that is not 0-9.

I do not want to run a function or proc. I have a script that is similar that just grabs the numeric value after text, it looks like this:

Update TableName set ColumntoUpdate=cast(replace(Columnofdirtydata,'Alpha #','') as int) where Columnofdirtydata like 'Alpha #%' And ColumntoUpdate is Null 

I thought it would work pretty good until I found that some of the data fields I thought would just be in the format Alpha # 12345789 are not.

Examples of data that needs to be stripped

AB ABCDE # 123 ABCDE# 123 AB: ABC# 123 

I just want the 123. It is true that all data fields do have the # prior to the number.

I tried substring and PatIndex, but I'm not quite getting the syntax correct or something. Anyone have any advice on the best way to address this?

Answer 1

You can use stuff and patindex.

stuff(Col, 1, patindex('%[0-9]%', Col)-1, '') 

SQL Fiddle

Answer 2

See this blog post on extracting numbers from strings in SQL Server. Below is a sample using a string in your example:

DECLARE @textval NVARCHAR(30) SET @textval = 'AB ABCDE # 123'  SELECT LEFT(SUBSTRING(@textval, PATINDEX('%[0-9.-]%', @textval), 8000),            PATINDEX('%[^0-9.-]%', SUBSTRING(@textval, PATINDEX('%[0-9.-]%', @textval), 8000) + 'X') -1)