Extract Numbers OR Characters From String

Below is the query to extract numbers or integers from an alphanumeric string.

PATINDEX () and STUFF() and a WHILE loop is used to get the result.

DECLARE @str VARCHAR(100) = 'hello123testing456'

DECLARE @hasPat INT = PATINDEX('%[a-z]%', @str)

WHILE @hasPat > 0

BEGIN

      SET @str = STUFF(@str, @hasPat, 1, '')

      SET @hasPat = PATINDEX('%[a-z]%', @str)

END

SELECT @str AS NumOnly

Result:

NumOnly

--------------------------------

123456

The above code can be modified to extract characters from the alphanumeric string by just changing the pattern inside PATINDEX() function

DECLARE @str VARCHAR(100) = 'hello123testing456'

DECLARE @hasPat INT = PATINDEX('%[0-9]%', @str)

WHILE @hasPat > 0

BEGIN

      SET @str = STUFF(@str, @hasPat, 1, '')

      SET @hasPat = PATINDEX('%[0-9]%', @str)

END

SELECT @str AS CharsOnly

Result:

CharsOnly

--------------------------------

hellotesting