Remove Non-Numeric Characters from SQL

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
srodgers45Asked:
Who is Participating?
 
Duy PhamFreelance IT ConsultantCommented:
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

0
 
srodgers45Author Commented:
Worked perfect. Thanks for the help. Much appreciated...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.