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 WHERE clause. Any pointers?

Thanks, Rajesh

回答1:

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

SELECT NCHAR(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;