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
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 = 7234reverse(reverse(value) + 0) = '4327'
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
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.
