We help IT Professionals succeed at work.

Remove Non-Numeric Characters from SQL

srodgers45
srodgers45 asked
on
Is there a way to remove all non numeric characters from SQL? I only want to see the numeric values in the IV Details section of the query shown below. No "cc", "ml", "<", etc...

Thanks in advance...

Query Sample
Comment
Watch Question

Freelance IT Consultant
Commented:
If your IV Details column only contains continuous numeric digits, then you can just use below query:
SELECT LEFT(SUBSTRING([IV Details], PATINDEX('%[0-9.-]%', [IV Details]), 2000), PATINDEX('%[^0-9.-]%', SUBSTRING([IV Details], PATINDEX('%[0-9.-]%', [IV Details]), 2000) + 'X') -1)

Open in new window

Note that if your IV Details could have length > 2000, replace 2000 in above query with larger value.

In case IV Details column could contain numeric digits which are not continuous, then you have to define an User-defined Function to strip non-numeric characters. For example:
CREATE FUNCTION [dbo].[udf_StripNonNumericCharacters](@str NVARCHAR(MAX)) RETURNS NVARCHAR(MAX)
AS
BEGIN
	WHILE PATINDEX('%[^0-9]%', @str) > 0
    BEGIN
        SET @str = STUFF(@str, PATINDEX('%[^0-9]%', @str), 1, '')
    END
	RETURN @str
END

Open in new window

Author

Commented:
Worked perfect. Thanks for the help. Much appreciated...