Remove Non Numeric Values Within SQL Select Statement

Question

I have a value in a database column VALUE:

C_4327

I need to strip the non numeric text from this so that it just leaves the numbers. I have tried using the REPLACE function within SQL but not I don't want to replace, just strip them out. I was previously using PHP to do this:

preg_replace("/[^0-9]/","",$row['VALUE']);

I'm retrieving the value in a SELECT statement.

Your help would be appreciated.

Thanks in advance

I would like to display the answer in my while loop below. So returning $valueID = $row['VALUE']; or similar if I did a REPLACE(...) AS companyID.. Is what I'm after but of course only displaying the numbers, not the other characters. — andy, Jan 28 '14 at 16:01
The replace function as part of a select statement will not change any data in the database. — , Jan 28 '14 at 16:02
I don't want to change anything in the database, I just want to strip it on the page. — andy, Jan 28 '14 at 16:05
This [Question](http://stackoverflow.com/questions/5146292/how-to-replace-non-numeric-characters-in-mysql) might Interest you! — Maheswaran Ravisankar, Jan 28 '14 at 16:06

score 2 · Accepted Answer · answered Jan 28 '14 at 16:19

2

If you want to get the number at the end of the string, you can use the following arcane approach:

select reverse(reverse(value) + 0) as NumberAtEnd;

In your case:

value ='C_4327'
reverse(value) = '7234_C'
reverse(value) + 0 = 7234
reverse(reverse(value) + 0) = '4327'

answered Jan 28 '14 at 16:19

Gordon Linoff

1,242,037
58
646
786

Thank you, this is exactly what I was looking for and it has worked. Thanks again. – andy Jan 28 '14 at 16:37

score 1 · Answer 2 · edited Jan 29 '14 at 10:12

you can create a Function like that/.

  CREATE FUNCTION GetNumeric
 (
 @strAlphaNumeric VARCHAR(256))
 RETURNS VARCHAR(256)
 AS BEGIN
 DECLARE @intAlpha INT
 SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
 BEGIN
 WHILE
 @intAlpha > 0
 BEGIN
 SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
 SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
 END
 END
 RETURN ISNULL(@strAlphaNumeric,0)
 END
 GO

then call the function as follows

   SELECT GetNumeric('123456789blahblahblah') AS filedname FROM your_table

you will get the answer : 123456789

score 0 · Answer 3 · answered Jan 28 '14 at 16:26

0

simple way: how about substring...

select substr( value, 3 ) from mytable;

it works on your example - but not sure if your real data is more complicated.

answered Jan 28 '14 at 16:26

Randy

16,480
1
37
55

Remove Non Numeric Values Within SQL Select Statement

3 Answers3