How to separate out characters from a string in SQL Server

Some times we will have a requirement to separate out characters from a string.

Find below the query which will do that for you:

DECLARE @strValue VARCHAR(25) = 'as456hrts@546'

DECLARE @AlphaPos INT

SET @AlphaPos = PATINDEX('%[^a-z]%', @strValue)

WHILE @AlphaPos > 0

BEGIN

SET @strValue = STUFF(@strValue, @AlphaPos, 1, '' )

SET @AlphaPos = PATINDEX('%[^a-z]%', @strValue )

END

SELECT @strValue

How to separate out numbers from a string in SQL Server

At a time we will have a requirement to seperate out numbers from a string.

Use below query to seperate out numbers from the string:

DECLARE @strValue VARCHAR(256) = 'asdf1234.a1s2d3f4@@@'
DECLARE @IntPos INT
SET @IntPos = PATINDEX('%[^0-9]%', @strValue)
WHILE @IntPos > 0
BEGIN
SET @strValue = STUFF(@strValue, @IntPos, 1, '' )
SET @IntPos = PATINDEX('%[^0-9]%', @strValue )
END
SELECT @strValue