How do I remove extended ASCII characters, except Carrage Return and Line Feed from a string in T-SQL?

Question

Wanted to post a solution to the above question, asked elsewhere, but don't have the reputation points, so have raised this and answered at the same time, as others may find this solution useful .. :

CREATE FUNCTION [dbo].[u_FN_CleanString](@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    ;
    WITH DummyTable(DummyColumn) AS (SELECT 1
                                     FROM   (VALUES (1),(1),(1),(1),(1),(1),(1),(1)) DummyTable(DummyColumn))

-- ASCII 0-31 (join 5 times: 2->4->8->16->32 rows)
     ,   Unprintables(Chrctr)    AS (SELECT TOP(32) CHAR(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)
                                     FROM   DummyTable a,DummyTable b,DummyTable c,DummyTable d,DummyTable e
                                        UNION
-- ASCII 128-255 (join 7 times: 2->4->8->16->32->64->128 rows)
                                     SELECT TOP(128) CHAR(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))+127)
                                     FROM   DummyTable a,DummyTable b,DummyTable c,DummyTable d,DummyTable e,DummyTable f,DummyTable g)
-- Remove extended ASCII characters
     SELECT @string=REPLACE(@string,Chrctr,'')
     FROM   Unprintables

-- Do not replace Carriage Returns or Line Feeds
     WHERE  Chrctr NOT IN (CHAR(10),CHAR(13))

    RETURN RTRIM(@string)
END

To format your posted code, XML or data samples, highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar (or basically just indent the whole code block by four spaces - that achieves the same result) — marc_s, Jul 14 '18 at 13:29
Please don't answer the question in the question post. You could answer your own question. But, seeing how the question is a duplicate, future readers will be better served by having all the answers in the same place. Sorry if our new-user rules are limiting you but it's not hard to get above the thresholds. Thanks. — Tom Blodget, Jul 14 '18 at 15:14
Possible duplicate of [How do I remove extended ASCII characters from a string in T-SQL?](https://stackoverflow.com/questions/15259622/how-do-i-remove-extended-ascii-characters-from-a-string-in-t-sql) — Tom Blodget, Jul 14 '18 at 15:15

John Cappelletti · Answer 1 · 2018-07-14T14:31:33.207

Too long for a comment.

Just something to consider. Your function may produce unexpected/undesirable results.

Let's assume you have a string 'Red{tab}{tab}{tab}hat', your function would return 'Redhat'.

So rather than @string=REPLACE(@string,Chrctr,'') perhaps use something less destructive @string=REPLACE(@string,Chrctr,' ') (notice the space)

Then we have a final clean-up to remove repeating spaces

Return ltrim(rtrim(replace(replace(replace(@string,' ','†‡'),'‡†',''),'†‡',' ')))

The value returned would be 'Red hat'.

Full Disclosure: The clean-up method was provided by Gordon some time ago. I just tweaked it to use more unique characters like '†‡' rather than '><'

How do I remove extended ASCII characters, except Carrage Return and Line Feed from a string in T-SQL?

1 Answers1