SQL Server过滤CJK标点符号 [英] SQL server filtering CJK punctuation characters

问题描述

我在sql server 2012数据库中有几个字符串，这些字符串具有CJK空间（大于一个空格）

I have few strings in sql server 2012 database that has a CJK space (larger than a space)

Unicode十进制：12288

Unicode decimal : 12288

十六进制：3000

我想编写一个SQL查询以使用WHERE子句对其进行过滤。

I would like to write a SQL query to filter them using WHERE clause. Any pointers?

谢谢，
Rajesh

Thanks, Rajesh

推荐答案

您可以使用 NCHAR（）函数创建Unicode字符：

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

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

您也可以使用在以下 WHERE 子句中，还包括将其与 REPLACE（）函数一起使用以摆脱它们。您只需要指定一个二进制排序规则（一个以 _BIN2 结尾的排序规则）以确保您不会替换任何其他转换为空格的字符（尽管我不确定至少在这种情况下，如果不使用二进制排序规则，则其净效果将有所不同。

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;

这篇关于SQL Server过滤CJK标点符号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！