SQL code to find and suppress numbers and special characters

Question

I want to include/modify in my SQL code to be able to eliminate the following characters: '&% -single quotes -ampersand -percentage

I need to include any of this characters in the current REGEX. Thanks in advance

DECLARE @counter int;
SET @counter = 0
WHILE(@counter < (SELECT MAX(ID_COLUMN) FROM [dbo].[zzIntegriyCheckV4HistoryClient_TEMP]))

BEGIN  
    IF  EXISTS (SELECT * FROM  [dbo].[zzIntegriyCheckV4HistoryClient_TEMP] WHERE MESSAGE NOT LIKE '% must be between %')
    WHILE 1 = 1
    BEGIN

        DECLARE @RetVal varchar(50)

        SET @RetVal =  (SELECT MESSAGE = STUFF(MESSAGE, PATINDEX('%[0-9()$%&/.:!]%', MESSAGE),1, '')
        FROM [dbo].[zzIntegriyCheckV4HistoryClient_TEMP]
        WHERE ID_COLUMN = @counter)

        IF(@RetVal IS NOT NULL)       
          UPDATE [dbo].[zzIntegriyCheckV4HistoryClient_TEMP] SET MESSAGE = @RetVal WHERE ID_COLUMN = @counter
        ELSE
            break
    END

    SET @counter = @counter + 1
END

If you read the description of the SQL tag you added, it encourages the addition of a tag for the specific DBMS you're using as well, as functionality and syntax differs between them. Please [edit] your post to do so. — Ken White, Apr 22 '20 at 02:30
The tag [tsql] points to SQL Server (although this is not sure). You might read [about TRANSLATE()](https://learn.microsoft.com/en-us/sql/t-sql/functions/translate-transact-sql?view=sql-server-ver15) (needs v2017+) — Shnugo, Apr 22 '20 at 09:43

score 0 · Answer 1 · answered Apr 22 '20 at 10:09

This is a blind shot as you did not specify your RDBMS. But assuming your [tsql] is pointing to SQL-Server and you have a version v2017+, you might try this:

--some muddy input

DECLARE @input VARCHAR(100)='abc123(33)%&$.def,,3xyz';

--a list of all characters you want to get rid of

DECLARE @notWanted VARCHAR(100)='0123456789!"§$%&/()=?*+-_#''<>@€,.;:';

--an intermediate placeholder (any of the forbidden characters)

DECLARE @useInstead CHAR(1)='~';

--Here you see the work of TRANSLATE()

SELECT TRANSLATE(@input,@notWanted,REPLICATE(@useInstead,LEN(@notWanted)));
--> abc~~~~~~~~~~~~def~~~xyz

--Now you can use a simple REPLACE()

SELECT REPLACE(TRANSLATE(@input,@notWanted,REPLICATE(@useInstead,LEN(@notWanted))),@useInstead,'')
--> abcdefxyz

Hint: Before anybody starts to offer a huge answer with a recursive CTE, loops, tally lists and so on please provide more details.
You might read this answer where I provided a function to deal with each character separately. You can adapt this to your needs.

SQL code to find and suppress numbers and special characters

1 Answers1