Sql Server Filtering Cjk Punctuation Characters

I have few strings in sql server 2012 database that has a CJK space (larger than a space) Unicode decimal : 12288 Hex: 3000 I would like to write a SQL query to filter them using W

Solution 1:

You can create a Unicode character using the NCHAR() function:

SELECTNCHAR(0x3000); -- http://unicode-table.com/en/3000/

You can also use that in a WHERE clause as follows, including also using it with the REPLACE() function to get rid of them. You just need to specify a binary collation (one ending in _BIN2) to ensure you are not replacing any other character that translate to a space (although I'm not sure that the net effect of that would be any different when not using a binary collation, at least in this scenario).

SELECT*FROM   [Table]
WHERE  [Column] LIKE N'%'+NCHAR(0x3000) + N'%'COLLATE Latin1_General_100_BIN2;

UPDATE tbl
SET    tbl.Column = REPLACE(tbl.[Column] COLLATE Latin1_General_100_BIN2,
                            NCHAR(0x3000),
                            N' ')
FROM   [Table] tbl
WHERE  [Column] LIKE N'%'+NCHAR(0x3000) + N'%'COLLATE Latin1_General_100_BIN2;