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


Posted on August 17th, 2015 by Hitesh Kumar | 624 Views

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

Related posts:

  1. What is PATINDEX in SQL Server?
  2. Contact Us
  3. Send email using smtp server.
  4. Brackets Problem(Determine whether a given string of parentheses is properly nested)