How to remove all emoji from SQL Server table How can I remove all the emoji icons from a SQL Server table?

I want to remove all   icons and keep only Hebrew and English Spaces.. letters

In the end I want to keep only Basic Latin (0020—007F) and Hebrew (0590—05FF).
One fairly simple method would be to use Regular Expressions via SQLCLR. For example:

DECLARE @Test NVARCHAR(500),
        @Expression NVARCHAR(500);

SET @Test = N'this' + NCHAR(0x0010) +
            N'$is % a&lt;&gt;TEST,;to}⌡↕strip╞╟╚══¶out_ç_ƒ▀ special-ĳ-೫-chars-舛-დ-א---B';

SET @Expression = N'[^\p{IsHebrew}\x20-\x7F]+';

SELECT @Test AS [Original],
       DATALENGTH(@Test) AS [OriginalBytes],
       SQL#.RegEx_Replace4k(@Test, @Expression, N'', -1, 1, NULL) AS [Cleaned],
   DATALENGTH(SQL#.RegEx_Replace4k(@Test, @Expression, N'', -1, 1, NULL)) AS [CleanedBytes];


returns:

this$is % a&lt;&gt;TEST,;to}⌡↕strip╞╟╚══¶out_ç_ƒ▀ special-ĳ-೫-chars-舛-დ-א---B        152

this$is % a&lt;&gt;TEST,;to}stripout__ special---chars---א---B                            112


The pattern / expression I used &mdash; [^\p{IsHebrew}\x20-\x7F]+ &mdash; will match 1 or more characters that are "not in the Hebrew Unicode block or in the range of 0x0000 - 0x007F", and replace them with an empty string.

I did also try a pattern of:

[^\p{IsHebrew}\p{IsBasicLatin}-[\p{Cc}]]


which should have been the same since "IsBasicLatin" is the range of 0x0000 - 0x007F, and then substract the Control Characters which have a range of 0x0000 - 0x001F. But for some reason the character class subtraction was not working.

To "clean" the table, you just put it in an UPDATE statement like so:

DECLARE @Expression NVARCHAR(500);

SET @Expression = N'[^\p{IsHebrew}\x20-\x7F]+';

UPDATE tbl
SET    tbl.field = SQL#.RegEx_Replace4k(tbl.field, @Expression, N'', -1, 1, NULL)
FROM   tbl
WHERE  SQL#.RegEx_IsMatch4k(tbl.field, @Expression, 1, NULL) = 1;


One difference between this pattern and the one used in my similar answer to Replace special characters in a column with space is the + at the end of the pattern here. That is what indicates "one or more" of whatever matches the thing to the left of it. In the case here, we are removing characters rather than replacing them individually with another character (such as a space, as was used in the other answer), so we can be a little more efficient and have the Regular Expression group several characters in a row to replace with the empty string.

To be fair, this could also be accomplished in pure T-SQL. You could create a UDF that uses PATINDEX to find characters to remove, and use STUFF(@String, position_from_PatIndex, 1, '') to remove them individually. Each time you would get the position from PATINDEX and exit / break if it returns 0. I don't see that being more efficient than the regular expression, especially since PATINDEX, unlike CHARINDEX, can't start at a specified location in the string, so you can't just pick up where you left off; you need to rescan the entire string each time you remove a single character.

I just tried the following and it returns the same result string (I just haven't put it into a UDF). Just add it to the end of the test above and run all of it together:

DECLARE @Position INT,
        @Pattern NVARCHAR(100);

SET @Pattern = N'%[^' + NCHAR(0x0020) + N'-' + NCHAR(0x007F)
                + NCHAR(0x0509) + N'-' + NCHAR(0x05FF) + N']%';
SET @Position = PATINDEX(@Pattern COLLATE Latin1_General_100_BIN2, @Test);

WHILE (@Position &gt; 0)
BEGIN
  SET @Test = STUFF(@Test, @Position, 1, N'');
  SET @Position = PATINDEX(@Pattern COLLATE Latin1_General_100_BIN2, @Test);
END;

SELECT @Test AS [CleanedViaT-SQL],
       DATALENGTH(@Test) AS [CleanedViaT-SQLBytes];


returns:

CleanedViaT-SQL                                                   CleanedViaT-SQLBytes

this$is % a&lt;&gt;TEST,;to}stripout__ special---chars---א---B          112