看下面的图片,任何一个人员的phone字段都有phone1到phone7。比如我给出一个搜索值135,那么输出张三和李四。当然,如果sql语句里面写phone1=135 or phone2=135.....也是可以实现的,但是有没有可以使用比如循环phone i=1 to 7 这样的类似手法?或者还有其他根据这个有规律的字段更高明的写法?
select distinct name,age from (select name,age,phone1 from ta union all select name,age,phone2 from ta .. .... select name,age,phone7) a where phone1 = '135'
select name from a where charindex(',135,',','+phone1+','+phone2+','+phone3+','+phone4+','+phone5+','+phone6+','+phone7+',')>0
select name from a where patindex('%,135,%',','+phone1+','+phone2+','+phone3+','+phone4+','+phone5+','+phone6+','+phone7+',')>0
根据你的规律。。select name from a where cast(phone1 as int) between 128 and 142
写错了select name from a where cast(phone1 as int) between 128 and 135
from (select name,age,phone1 from ta union all
select name,age,phone2 from ta ..
....
select name,age,phone7) a
where phone1 = '135'
select name from a
where charindex(',135,',','+phone1+','+phone2+','+phone3+','+phone4+','+phone5+','+phone6+','+phone7+',')>0
where patindex('%,135,%',','+phone1+','+phone2+','+phone3+','+phone4+','+phone5+','+phone6+','+phone7+',')>0
where cast(phone1 as int) between 128 and 142
where cast(phone1 as int) between 128 and 135
PATINDEX与CHARINDEXPATINDEX函数支持使用通配符,可以用在很多有变化的查找中。而CHARINDEX不可以。根据你自己不同的情况,这两个函数对你在SQL Server中的字符串的搜索、控制、分析很有帮助。