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