现在有一个数据类似于
列名 UserName
数据 user001
user002
...
UserWang
...
WangYiYi
...想在存储过程中查找出 数据格式为 user + 数字的数据集,怎么样实现?
不能使用SQL CLR,服务器不支持
求教大神
列名 UserName
数据 user001
user002
...
UserWang
...
WangYiYi
...想在存储过程中查找出 数据格式为 user + 数字的数据集,怎么样实现?
不能使用SQL CLR,服务器不支持
求教大神
select userName
from tb
where userName like 'user%' and len(userName) > 4 and isnumeric(right(userName,len(userName)-4)) = 1
create table tb(userName varchar(20))
insert into tb
select 'user002' union all
select 'user002aaa' union all
select 'usera0a12'
goselect userName
from tb
where userName like 'user%' and len(userName) > 4 and isnumeric(right(userName,len(userName)-4)) = 1drop table tb/**********userName
--------------------
user002(1 行受影响)
where UserName like 'user[0-9][0-9]%'
select 'user002' as UserName union all
select 'user002aaa' union all
select 'usera0a12' union all
select 'user1234567851'
) as t
where UserName like 'user[0-9]%'
and UserName not like 'user%[^0-9]%'
select 'user002' as UserName union all
select 'user002aaa' union all
select 'usera0a12' union all
select 'user1234567851'
) as t
where UserName like 'user%'
and UserName not like 'user%[^0-9]%'
后面的这个模糊查询是什么意思?能解释一下吗?不懂...
如果是正则,那么能在2000的服务其下运行,为什么不能写成'user[0-9]+%'?
insert into tb
select 'user0s0u2' union all
select 'user002aaa' union all
select 'usera0a12'
goselect * from tb where userName like 'user%' and
PATINDEX('%[^0-9]%',RIGHT(userName,len(userName)-4))=0/*
userName
--------------------
user002(1 行受影响)