select * from tt where
case when length(呼入)>=7 and length(relationname)>=7 then
substr(呼入,length(呼入)-7,7)=substr(relationname,length(relationname)-7,7)
else
relationname=呼入 end
case when length(呼入)>=7 and length(relationname)>=7 then
substr(呼入,length(呼入)-7,7)=substr(relationname,length(relationname)-7,7)
else
relationname=呼入 end
where (length('callINnum')>=7
and length(num)>=7
and substr(num,length(num)-7)=substr('callINnum',length('callINnum')-7)
) or 'callINnum'=num;
...> where (length('13959112113')>=7
...> and length(num)>=7
...> and substr(num,length(num)-6)=substr('13959112113',length('13959112113')-6)
...> ) or '13959112113'=num;
relationname
张三手机
sqlite> select relationname from numtable
...> where (length('13339112113')>=7
...> and length(num)>=7
...> and substr(num,length(num)-6)=substr('13339112113',length('13339112113')-6)
...> ) or '13339112113'=num;
relationname
张三手机
sqlite> select relationname from numtable
...> where (length('13959112110')>=7
...> and length(num)>=7
...> and substr(num,length(num)-6)=substr('13959112110',length('13959112110')-6)
...> ) or '13959112110'=num;
sqlite> select relationname from numtable
...> where (length('13959110086')>=7
...> and length(num)>=7
...> and substr(num,length(num)-6)=substr('13959110086',length('13959110086')-6)
...> ) or '13959110086'=num;
sqlite> select relationname from numtable
...> where (length('1008')>=7
...> and length(num)>=7
...> and substr(num,length(num)-6)=substr('1008',length('1008')-6)
...> ) or '1008'=num;
sqlite> select relationname from numtable
...> where (length('10086')>=7
...> and length(num)>=7
...> and substr(num,length(num)-6)=substr('10086',length('10086')-6)
...> ) or '10086'=num;
relationname
移动客服
sqlite> select relationname from numtable
...> where (length('13958776532')>=7
...> and length(num)>=7
...> and substr(num,length(num)-6)=substr('13958776532',length('13958776532')-6)
...> ) or '13958776532'=num;
relationname
张三家
sqlite>
sqlite> select relationname from numtable
...> where (length('8776532')>=7
...> and length(num)>=7
...> and substr(num,length(num)-6)=substr('8776532',length('8776532')
-6)
...> ) or '8776532'=num;
relationname
张三家
sqlite>
+-------------+--------------+
| num | relationname |
+-------------+--------------+
| 10086 | 移动克服 |
| 110 | 匪警 |
| 8776532 | 胡锦涛家 |
| 13959112113 | 本拉登手机 |
+-------------+--------------+
4 rows in set (0.00 sec)mysql> set@num=110;
Query OK, 0 rows affected (0.00 sec)mysql> select num,relationname from numtable where if(length(num>6&&right(num,7)
=@num),num,num)=@num;
+------+--------------+
| num | relationname |
+------+--------------+
| 110 | 匪警 |
+------+--------------+
1 row in set (0.00 sec)
mysql> set @num="8776532";
Query OK, 0 rows affected (0.00 sec)
mysql> select num,relationname from numtable where if(length(num>6&&right(num,7)
=@num),num,"")=@num;
+---------+--------------+
| num | relationname |
+---------+--------------+
| 8776532 | 胡锦涛家 |
+---------+--------------+
1 row in set (0.00 sec)
傻了吧我不匹配")) as name from numtable;
+----------------+
| name |
+----------------+
| 傻了吧我不匹配 |
| 110 |
| 傻了吧我不匹配 |
| 傻了吧我不匹配 |
+----------------+
4 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)mysql> select if(length(num)>6&&right(num,7)=@num,relationname,if(num=@num,relat
ionname,"")) as name from numtable having name!="";
+------+
| name |
+------+
| 匪警 |
+------+
1 row in set (0.00 sec)