How to find a special characters in a string and replace it with a space in SQL?

Question

I have contact_firstname column which has some special characters like (@,&,-,_, etc) in the data stored in that column. I want to first find all those special characters in each record and replace those characters with a space. I found a query on this website which helps identify the special characters but I am not sure how to find charindex of each special character in the below string and replace it with a space.

DECLARE @MyString VARCHAR(100)
SET @MyString = '!Char$Fox@'

IF (@MyString LIKE '%[^a-zA-Z0-9]%')
   BEGIN
      PRINT 'Contains "special" characters'

    END

Write a function that loops through each character one at a time, and if it is a special character, replaces it with a space. — Tab Alleman, Mar 18 '15 at 18:58
Do you want to display spaces for “special characters” on the screen, output, or `PRINT` statements, or do you want to replace these characters in the database? — Dour High Arch, Mar 18 '15 at 19:01

score 0 · Accepted Answer · answered Mar 18 '15 at 19:07

0

I think you have to loop, as Tab Alleman mentioned:

declare @MyString varchar(100) = '!Char$Fox@'
declare @i int = 0
declare @char varchar(1)
declare @len int = LEN(@MyString)
declare @result varchar(100) = ''

while @i < @len
begin
    set @char = SUBSTRING(@MyString, @i, 1)

    if @char like '%[^a-zA-Z0-9]%'
    begin
        set @char = ' '
    end

    set @result = @result + @char
    set @i = @i + 1
end

select @result

answered Mar 18 '15 at 19:07

Chris Steele

1,343
1
9
20

There is a REPLACE function, but it works one character at a time, and your pattern is really just trying to find any character that isn't an alpha-numeric, so the number of cases would be prohibitive. – Chris Steele Mar 18 '15 at 19:08

score 0 · Answer 2 · edited May 23 '17 at 11:50

You can also do this:

DECLARE @InvalidChars VARCHAR(100)
DECLARE @MyString VARCHAR(100)

SET @InvalidChars = '!$@'
SET @MyString = '!Char$Fox@'

;WITH CTE AS
(
  SELECT SUBSTRING(@InvalidChars, 1, 1) AS [String], 1 AS [Start], 1 AS [Counter]
  UNION ALL
  SELECT SUBSTRING(@InvalidChars, [Start] + 1, 1) AS [String], [Start] + 1, [Counter] + 1 
  FROM CTE 
  WHERE [Counter] < LEN(@InvalidChars)
)

SELECT @MyString = REPLACE(@MyString, CTE.[String], ' ') FROM CTE

SELECT @MyString

Result:

Char Fox

This is a combination of solutions found here:

How to Replace Multiple Characters in SQL?

T-SQL: Opposite to string concatenation - how to split string into multiple records [duplicate]

How to find a special characters in a string and replace it with a space in SQL?

2 Answers2