日期:2014-05-18  浏览次数:20583 次

SQL中能实现这种查询吗?
SQL code
一般我们用SQL模糊查询是这样的
例如从字段str中选择带有key的记录
SELECT * FROM TABLE1
WHERE str like '%key%'
字段str在key中
高手们怎么实现?
例如有一记录中str为"abc"而查找关键词为"fgabcde"
我想找到str为abc的这条记录该怎么样写呢?



------解决方案--------------------
SQL code
declare @T table(str nvarchar(10))
insert @T select 'saKeybcc'
insert @T select 'Keybcc'
insert @T select 'Key'

SELECT * FROM @T WHERE str like '%_key_%'--加一个"_",key前后最少要有一个字符


select * from @T where Str like 'key'
--或
select * from @T where Str= 'key'




str        
---------- 
saKeybcc

(所影响的行数为 1 行)

str        
---------- 
Key

(所影响的行数为 1 行)

str        
---------- 
Key

(所影响的行数为 1 行)

------解决方案--------------------
SQL code
declare @T table(str nvarchar(10))
insert @T select 'abc'
insert @T select 'fga'
insert @T select 'Key'

select * from @t where PATINDEX('%'+str+'%' , 'fgabcde')>0

------解决方案--------------------
SQL code
create table #tab1(id1 int null,id2 int null,id3 int null) 
go
insert into #tab1(id1,id2,id3)
select id1,id2,id3 from 
(
    select 1 as id1,1 as id2,1 as id3 union 
    select 1,2,3 
) as tab
go
create table #tab(id1 int null,id2 int null,id3 int null)
go
declare @id1 int 
declare curst cursor static for
select id1 from #tab1 group by id1
open curst
fetch next from curst into @id1
while @@fetch_status = 0 
begin
    insert #tab(id1,id2,id3) select top 1 id1,id2,id3 from #tab1
    fetch next from curst into @id1
end
close curst
deallocate curst
select * from #tab
go
drop table #tab1
drop table #tab
go table #tab1(id1 int null,id2 int null,id3 int null) 
go
insert into #tab1(id1,id2,id3)
select id1,id2,id3 from 
(
    select 1 as id1,1 as id2,1 as id3 union 
    select 1,2,3 
) as tab
go
create table #tab(id1 int null,id2 int null,id3 int null)
go
declare @id1 int 
declare curst cursor static for
select id1 from #tab1 group by id1
open curst
fetch next from curst into @id1
while @@fetch_status = 0 
begin
    insert #tab(id1,id2,id3) select top 1 id1,id2,id3 from #tab1
    fetch next from curst into @id1
end
close curst
deallocate curst
select * from #tab
go
drop table #tab1
drop table #tab
go,id3 from 
(
    select 1 as id1,1 as id2,1 as id3 union 
    select 1,2,3 
) as tab
go
create table #tab(id1 int null,id2 int null,id3 int null)
go
declare @id1 int 
declare curst cursor static for
select id1 from #tab1 group by id1
open curst
fetch next from curst into @id1
while @@fetch_status = 0 
begin
    insert #tab(id1,id2,id3) select top 1 id1,id2,id3 from #tab1
    fetch next from curst into @id1
end
close curst
deallocate curst
select * from #tab
go
drop table #tab1
drop table #tab
go