declare @t table (col varchar(50))insert @t select 'dfdfdf abcd' union all select 'dfdfdf bced' union all select 'dfdfdf' union all select 'ffffg dddd'select * from @t where col like '%dfd%abc%' or (col like '%dfd%' and charindex(' ',col)=0)col -------------------------------------------------- dfdfdf abcd dfdfdf(所影响的行数为 2 行)
declare @t table(name varchar(50)) insert into @t select 'dfdfdf abcd' insert into @t select 'dfdfdf bced' insert into @t select 'dfdfdf' insert into @t select 'ffffg dddd' select * from @t where name like 'dfd% abc%' or (name like 'dfd%' and charindex(' ',rtrim(name))=0)/* name ---------------- dfdfdf abcd dfdfdf */
select * from tb where name like 'dfd%abc%' or (name like 'dfd%' and charindex(' ',name)=0)
对不起,把第一个单词为fffee and 把第二个单词前三个字母为dfd, and ( 第三个单词前三个字母为abc 或者 第三个单词不存在 ) 的这些字段取出来
declare @t table (col varchar(50))insert @t select 'fffee dfdfdf abcd' union all select 'fffee dfdfdf bced' union all select 'fffee dfdfdf' union all select 'rrrrr ffffg dddd'select * from @t where col like '%fff%dfd%abc%' or (col like '%fff%dfd%' and parsename(replace(rtrim(col),' ','.'),3) is null)col -------------------------------------------------- fffee dfdfdf abcd fffee dfdfdf(所影响的行数为 2 行)
(col varchar(50))insert @t
select 'dfdfdf abcd' union all
select 'dfdfdf bced' union all
select 'dfdfdf' union all
select 'ffffg dddd'select * from @t where col like '%dfd%abc%'
or (col like '%dfd%' and charindex(' ',col)=0)col
--------------------------------------------------
dfdfdf abcd
dfdfdf(所影响的行数为 2 行)
insert into @t select 'dfdfdf abcd'
insert into @t select 'dfdfdf bced'
insert into @t select 'dfdfdf'
insert into @t select 'ffffg dddd'
select
*
from
@t
where
name like 'dfd% abc%'
or
(name like 'dfd%' and charindex(' ',rtrim(name))=0)/*
name
----------------
dfdfdf abcd
dfdfdf
*/
or (name like 'dfd%' and charindex(' ',name)=0)
fffee dfdfdf abcd
fffee dfdfdf bced
fffee dfdfdf
rrrrr ffffg dddd
现在要写一个sql 语句,把第一个单词前三个字母为fffee 把第二个单词前三个字母为dfd,
第三个单词前三个字母为abc 或者第三个单词不存在的这些字段取出来
结果也就是把下边两条记录取出
fffee dfdfdf abcd
fffee dfdfdf
还请 不吝赐教,非常感谢
and ( 第三个单词前三个字母为abc 或者 第三个单词不存在 ) 的这些字段取出来
(col varchar(50))insert @t
select 'fffee dfdfdf abcd' union all
select 'fffee dfdfdf bced' union all
select 'fffee dfdfdf' union all
select 'rrrrr ffffg dddd'select * from @t where col like '%fff%dfd%abc%'
or (col like '%fff%dfd%' and parsename(replace(rtrim(col),' ','.'),3) is null)col
--------------------------------------------------
fffee dfdfdf abcd
fffee dfdfdf(所影响的行数为 2 行)