一个UNICODE字符的有关问题

一个UNICODE字符的问题

SQL code

create table #tmp(col nvarchar(10))--建表goinsert into #tmp select N'012³456789' union all select N'0123456789' select * from #tmp   --查询where charindex(N'³',col)<>0--where col like '%³%'

表：
col
----------
012³456789
0123456789

我用上面的查询语句查询2条记录都会出来。。。
求SQL用LIKE或CHARINDEX查出只含'³'的记录，谢谢。



[解决办法]

SQL code

WITH    cte          AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY col ) AS row ,                        col               FROM     #tmp             )    SELECT  col    FROM    cte    WHERE   row IN (            SELECT  row            FROM    ( SELECT    a.row ,                                SUBSTRING(a.col, b.number + 1, 1) AS col                      FROM      cte a ,                                master.dbo.spt_values b                      WHERE     LEN(a.col) > b.number                                AND b.type = 'p'                    ) a            WHERE   UNICODE(col) = UNICODE(N'³') )/*col----------012³456789(1 個資料列受到影響)*/
[解决办法]
SQL code
create table #tmp(col nvarchar(10))--建表goinsert into #tmp select N'012³456789' union all select N'0123456789' select * from #tmp   --查询where charindex(N'³' COLLATE Chinese_PRC_CI_AS_KS_WS,col) > 0drop table #tmp/************col----------012³456789(1 行受影响)排序规则，区分假名！ 

 

查看更多 下一篇