表中patient 有列name,name 中有5行数据 ,陈土刘,刘施土,库萨克,东阿呢,刘妹
现在要查找跟 '刘土妹'有两个以上字相同的,结果应该为陈土刘和刘妹,sql 应该怎样写?
现在要查找跟 '刘土妹'有两个以上字相同的,结果应该为陈土刘和刘妹,sql 应该怎样写?
解决方案 »
- 求一SQL查询语句
- SQL Server 惊天大陷井!!! 大家看看你有未曾中招! 也烦高手进来支招!!!
- 无法删除用户,提示拥有对象!
- 求高手给解答这个sql语句怎么写啊。。谢谢了。
- 在线求助!
- 怎么把 WINDOWS验证的模式改成SQL验证和WINDOWS验证的混合模式?
- 哪里能够下载Sqlserver2000 的开发版或个人版?
- 求解一个很难的问题,高分求解!!!!急!急!!!!!!
- 当两个表的CompinVestor.CompID =SBCompany.CompID,把SBCompany.InvDevice赋给CompinVestor.InvDevice
- 新手,这样的触发器如何写?
- 怎么在数据库里随机查一个数据?
- SQL用户自定义函数 和 CHECK约束 急!急!急!
if object_id('[patient]') is not null drop table [patient]
go
create table [patient] (name varchar(6))
insert into [patient]
select '陈土刘' union all
select '刘施土' union all
select '库萨克' union all
select '东阿呢' union all
select '刘妹'
select *,0 as n into #t from [patient]declare @n int,@s varchar(10),@s1 varchar(10)
set @s1='刘土妹'set @n=1
set @s=substring(@s1,@n,1)
while @n<=len(@s1)
begin
update #t set n=n+1 where charindex(@s,name)>0
set @n=@n+1
end
go
select * from #t where n>=2
name n
------ -----------
陈土刘 3
刘施土 3
刘妹 3(3 行受影响)
drop table #t
if object_id('[patient]') is not null drop table [patient]
go
create table [patient] (name varchar(6))
insert into [patient]
select '陈土刘' union all
select '刘施土' union all
select '库萨克' union all
select '东阿呢' union all
select '刘妹'
select *,0 as n into #t from [patient]declare @n int,@s varchar(10),@s1 varchar(10)
set @s1='刘土妹'set @n=1
set @s=substring(@s1,@n,1)
while @n<=len(@s1)
begin
update #t set n=n+1 where charindex(@s,name)>0
set @n=@n+1
set @s=substring(@s1,@n,1)
end
go
select * from #t where n>=2name n
------ -----------
陈土刘 2
刘施土 2
刘妹 2(3 行受影响)
drop table #t
(
name nvarchar(25)
)
insert into patient
select '陈土刘' union all
select '刘施土' union all
select '库萨克' union all
select '东阿呢' union all
select '刘妹' select * from patient
where len(name)-len(replace(replace(replace(name,'刘',''),'土',''),'妹',''))>=2
-------------------
name
陈土刘
刘施土
刘妹
--> 测试数据: [patient]
if object_id('[patient]') is not null drop table [patient]
go
create table [patient] (name varchar(6))
insert into [patient]
select '陈土刘' union all
select '刘施土' union all
select '库萨克' union all
select '东阿呢' union all
select '刘妹'
goselect name
from (
select a.name,b.c1
from patient a
cross apply (select c1=substring(a.name,number,1) from master..spt_values where type = 'P' and number between 1 and len(a.name)) b
join (select c1='刘' union all select c1='土' union all select c1='妹') c on b.c1 = c.c1
) a
group by name having count(1) >= 2/*
name
------
陈土刘
刘妹
刘施土(3 行受影响)
*/