How to remove specific characters from a string in SQL Server?


In this example we are using Patindex and Stuff functions. Patindex is used to find the specific character occurrence position and stuff is used to replace that character position with blank space.

DECLARE @RemoveChar VARCHAR(50),
@String_Exp VARCHAR(500) = 'Hi123t4e5s6h'

SET @RemoveChar = '%[0-9]%' -- Remove All Integer

-- SET @RemoveChar = '%[a-z]%'  Remove All Alphabets

WHILE PATINDEX(@RemoveChar, @String_Exp) > 0
SET @String_Exp = STUFF(@String_Exp, PATINDEX(@RemoveChar, @String_Exp), 1,'')

Select @String_Exp


Result : Hitesh