create table #tmp(col nvarchar(10))--建表
go
insert 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查出只含'³'的记录,谢谢。
where charindex(N'³',col)>0--大于0
where col like N'³'--orwhere charindex(N'³',col) = 1 and len(col) = 1
where col like N'%³%'--orwhere charindex(N'³',col) > 0
----------
012³456789
0123456789(2 行受影响)
还是2行都搜的出来,另外1楼和2楼的请先试一下
如果是这个字符在字符串里,然后想LIKE或CHARINDEX出来就不知道用什么方法了呢,除非写个函数拆分然后再单个比较
select UNICODE(N'³'),UNICODE(N'3')
----------- -----------
179 51(所影响的行数为 1 行)
但是直接比较就是不行,蛮奇怪的
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 個資料列受到影響)*/
create table #tmp(col nvarchar(10))--建表
go
insert 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 行受影响)
排序规则,区分假名!
create table #tmp(col nvarchar(10))--建表
go
insert into #tmp
select N'012³456789' union all
select N'0123456789'
select * from #tmp --查询
where charindex(N'³' COLLATE Chines……
/*
-----
true(所影响的行数为 1 行)
like的话就 where col like N'%³%' COLLATE Chinese_PRC_CI_AS_KS_WS 就行了。