22,209
社区成员
发帖
与我相关
我的任务
分享
select id from 表 where cf like'%语文%' and cf like'%数学%' and len(cf)-len(replace(cf, ',',''))=1
也就是查询包含“语文”,而且也包含“数学”,而且只有一个“,”的记录。
select * from tablename
where REPLACE( dbo.get_segment1(CF) ,',','') in('语文数学' ,'数学语文')
--创建分隔函数
create FUNCTION [dbo].[get_segment1](@text nvarchar(20))
RETURNS nvarchar(20)
AS
BEGIN
DECLARE @len int
DECLARE @segment nvarchar(20), @iSegment int
DECLARE @ch nvarchar(1), @iCh int
DECLARE @segType int, @chType int -- 0 数字, 1 其他
SET @len = LEN(@text)
SET @iCh = 1
WHILE @iCh <= @len
BEGIN
SET @ch = SUBSTRING(@text, @iCh, 1)
SET @chType = CASE WHEN @ch BETWEEN N'0' AND N'9' THEN 0
ELSE 1
END
IF @iCh = 1
BEGIN
SET @segType = @chType
SET @segment = @ch
SET @iSegment = 1
END
ELSE IF @chType=1
begin
SET @segment = @segment + @ch
END
SET @iCh = @iCh+1
END
RETURN @segment
END
GO
--运行代码 试试