select * from tele where
(substring(reverse(phone),1,1) = substring(reverse(phone),2,1) and substring(reverse(phone),3,1) = substring(reverse(phone),4,1)) or
(substring(reverse(phone),1,1) = substring(reverse(phone),3,1) and substring(reverse(phone),2,1) = substring(reverse(phone),4,1)) 第三个条件怎么搞,没想明白,再顶。
(substring(reverse(phone),1,1) = substring(reverse(phone),2,1) and substring(reverse(phone),3,1) = substring(reverse(phone),4,1)) or
(substring(reverse(phone),1,1) = substring(reverse(phone),3,1) and substring(reverse(phone),2,1) = substring(reverse(phone),4,1)) 第三个条件怎么搞,没想明白,再顶。
这是是指:逐一加一吗?
(substring(reverse(phone),1,1) = substring(reverse(phone),2,1) and substring(reverse(phone),3,1) = substring(reverse(phone),4,1)) or
(substring(reverse(phone),1,1) = substring(reverse(phone),3,1) and substring(reverse(phone),2,1) = substring(reverse(phone),4,1)) or
(
cast(substring(reverse(phone),1,1) as int) = cast(substring(reverse(phone),2,1) as int) - 1 and
cast(substring(reverse(phone),2,1) as int) = cast(substring(reverse(phone),3,1) as int) - 1 and
cast(substring(reverse(phone),3,1) as int) = cast(substring(reverse(phone),4,1) as int) - 1
)
drop table tele
create table tele(phone char(20))
insert tele
select '18581122' union
select '18581212' union
select '18587645' union
select '18581234' union
select '18581711' union
select '18589898'select * from tele where
(substring(ltrim(reverse(phone)),1,1) = substring(ltrim(reverse(phone)),2,1) and substring(ltrim(reverse(phone)),3,1) = substring(ltrim(reverse(phone)),4,1)) or
cast(substring(ltrim(reverse(phone)),1,1) as int) = cast(substring(ltrim(reverse(phone)),3,1) as int) and cast(substring(ltrim(reverse(phone)),2,1) as int) = cast(substring(ltrim(reverse(phone)),4,1) as int) or
(
cast(substring(ltrim(reverse(phone)),1,1) as int) = cast(substring(ltrim(reverse(phone)),2,1) as int) + 1 and
cast(substring(ltrim(reverse(phone)),2,1) as int) = cast(substring(ltrim(reverse(phone)),3,1) as int) + 1 and
cast(substring(ltrim(reverse(phone)),3,1) as int) = cast(substring(ltrim(reverse(phone)),4,1) as int) + 1
)
--生成测试数据
if object_id('tele') is not null
drop table tele
create table tele(phone char(20))
insert tele
select '18581122' union
select '18581212' union
select '18587645' union
select '18581234' union
select '18581711' union
select '18589898'
--以下是你要的代码
select * from tele where
(substring(ltrim(reverse(phone)),1,1) = substring(ltrim(reverse(phone)),2,1) and substring(ltrim(reverse(phone)),3,1) = substring(ltrim(reverse(phone)),4,1)) or
cast(substring(ltrim(reverse(phone)),1,1) as int) = cast(substring(ltrim(reverse(phone)),3,1) as int) and cast(substring(ltrim(reverse(phone)),2,1) as int) = cast(substring(ltrim(reverse(phone)),4,1) as int) or
(
cast(substring(ltrim(reverse(phone)),1,1) as int) = cast(substring(ltrim(reverse(phone)),2,1) as int) + 1 and
cast(substring(ltrim(reverse(phone)),2,1) as int) = cast(substring(ltrim(reverse(phone)),3,1) as int) + 1 and
cast(substring(ltrim(reverse(phone)),3,1) as int) = cast(substring(ltrim(reverse(phone)),4,1) as int) + 1
)
drop table tele
create table tele(phone char(20))
insert tele
select '18581122' union
select '18581212' union
select '18587645' union
select '18581234' union
select '18581711' union
select '18589898'select * from tele where
(substring(ltrim(reverse(phone)),1,1) = substring(ltrim(reverse(phone)),2,1) and substring(ltrim(reverse(phone)),3,1) = substring(ltrim(reverse(phone)),4,1)) or
cast(substring(ltrim(reverse(phone)),1,1) as int) = cast(substring(ltrim(reverse(phone)),3,1) as int) and cast(substring(ltrim(reverse(phone)),2,1) as int) = cast(substring(ltrim(reverse(phone)),4,1) as int) or
(
cast(substring(ltrim(reverse(phone)),1,1) as int) = cast(substring(ltrim(reverse(phone)),2,1) as int) + 1 and
cast(substring(ltrim(reverse(phone)),2,1) as int) = cast(substring(ltrim(reverse(phone)),3,1) as int) + 1 and
cast(substring(ltrim(reverse(phone)),3,1) as int) = cast(substring(ltrim(reverse(phone)),4,1) as int) + 1
)