关于select 查询语句?请教

发布网友发布时间：2022-05-10 19:08

共2个回答

热心网友时间：2023-10-17 14:46

select *
from person
where isnumeric(姓名)=1 and 姓名 like '[a-z]' or 姓名 like'[a-z][a-z]' or 姓名 like'[a-z][a-z][a-z]'

应你的要求，我自己写了两个函数，已经实现这个功能。因为查询表是记录集，所以得到你的程序中执行这个函数。
数字：
CREATE FUNCTION Judge_IsNumeric
(
@pString VARCHAR(4000)
)
RETURNS int
WITH ENCRYPTION
AS
BEGIN
DECLARE @vJudge int
DECLARE @len int
set @len=1
DECLARE @columlen int
set @columlen=len(@pString)
while @len<=@columlen
begin
if PATINDEX('[0-9]', substring(@pString,@len,1)) = 1
begin
set @vJudge=1
end
if PATINDEX('[0-9]',substring(@pString,@len,1)) = 0
begin
set @vJudge=0
break
end
select @len=@len+1
end
return @vJudge
end /*返回值0代表字符，1代表数字（匹配成功）。*/
同理，把[0-9]改为[a-z]即可判读是不是a-z 和A-Z的字符。

字符：
CREATE FUNCTION Fdq_IsNumeric
(
@pString VARCHAR(4000)
)
RETURNS int
WITH ENCRYPTION
AS
BEGIN
DECLARE @vJudge int
DECLARE @len int
set @len=1
DECLARE @columlen int
set @columlen=len(@pString)
while @len<=@columlen
begin
if PATINDEX('[a-z]', lower(substring(@pString,@len,1))) = 1
begin
set @vJudge=1
end
if PATINDEX('[a-z]',lower(substring(@pString,@len,1))) = 0
begin
set @vJudge=0
break
end
select @len=@len+1
end
return @vJudge
end

由于是标量值函数所以调用的时候采用:
select Judge_IsNumeric('asdadsfasdf')

热心网友时间：2023-10-17 14:47

declare @t table(name varchar(10))
insert into @t
select '123abc' union
select 'a1b2c3' union
select 'aaab3ccccc' union
select 'abcde' union
select '112233v' union
select 'a1234' union
select '54321' union
select '3cccc3'

select * from @t

select * from @t where
ISNUMERIC(name)=1 -- or name not like '%[^0-9]%' --纯数字
or name not like '%[^a-zA-Z]%' --纯字母

--查询结果----------------------
(8 row(s) affected)
name
----------
112233v
123abc
3cccc3
54321
a1234
a1b2c3
aaab3ccccc
abcde

(8 row(s) affected)

name
----------
54321
abcde

(2 row(s) affected)