select * from table where group_no like'/[[0-9][0-9][0-9][0-9]/]' escape '/' 只能查询出组号[8976] 2边括号中间4位数字的组 现在我的问题的组号长度不固定 但是要符合2边中括号中间4个数字的格式 [9864][9845][4832].....长度不固定 请问高手们有什么办法错别字多了不好意思
--加%号select * from [table] where group_no like '%/[[0-9][0-9][0-9][0-9]/]%' escape '/'
select * from table where group_no not like'/[[^0-9][^0-9][^0-9][^0-9]/]' escape '/'
这个可能要自己写函数去判断,光是用like似乎不行
在正则表达式中好像是可以用+好表示后面的跟随字符也要符合前面指定的规范 sql 里面不可以吗
--> 测试数据: [tb] if object_id('[tb]') is not null drop table [tb] create table [tb] (group_no varchar(200)) insert into [tb] select '[4535][4353]' union all select '[4535][4353][4535][4353]' union all select '[4535][4353][4535][4353]' union all select '[4535][4353]123' union all select '123[4535][4353]123' go --创建函数 create function ps(@group_no varchar(200)) returns int as begin declare @flag int declare @temp varchar(4) while(left(@group_no,1)='[') begin set @temp=substring(@group_no,2,4) if(charindex('^[0-9]',@temp)>0) begin set @flag=0 continue end else set @flag=1 set @group_no=substring(@group_no,7,len(@group_no)) end if(@group_no!='') set @flag=0 return @flag end go--调用: select * from [tb] where dbo.ps(group_no)=1 --结果: group_no ------------------------ [4535][4353] [4535][4353][4535][4353] [4535][4353][4535][4353]
USE tempdb go CREATE TABLE Test ( seqno INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Item NVARCHAR(360) NOT NULL DEFAULT('[0000]') ) SELECT * FROM Test INSERT INTO Test(Item) VALUES('[0123][1234][5678]') INSERT INTO Test(Item) VALUES('[0123][4568][5678]') INSERT INTO Test(Item) VALUES('[0123][4586][5678]') INSERT INTO Test(Item) VALUES('[0123][4875][5678]') INSERT INTO Test(Item) VALUES('[0123][0][5678]') INSERT INTO Test(Item) VALUES('[0123][5896][5678]')DECLARE @IfVar NVARCHAR(300),@NMax INT ,@Pos INT SELECT @IfVar='',@NMax=3,@Pos=1 WHILE @Pos <= @NMax BEGIN SELECT @IfVar=@IfVar+'/[[0-9][0-9][0-9][0-9]/]' SELECT @Pos=@Pos+1 END --PRINT @IfVar SELECT * FROM Test WHERE Item LIKE @IfVar ESCAPE '/' --SELECT * FROM Test
--加%号select * from [table] where group_no like '%/[[0-9][0-9][0-9][0-9]/]%' escape '/'
if object_id('[tb]') is not null drop table [tb]
create table [tb] (group_no varchar(200))
insert into [tb]
select '[4535][4353]' union all
select '[4535][4353][4535][4353]' union all
select '[4535][4353][4535][4353]' union all
select '[4535][4353]123' union all
select '123[4535][4353]123'
go
--创建函数
create function ps(@group_no varchar(200))
returns int
as
begin
declare @flag int
declare @temp varchar(4)
while(left(@group_no,1)='[')
begin
set @temp=substring(@group_no,2,4)
if(charindex('^[0-9]',@temp)>0)
begin
set @flag=0
continue
end
else
set @flag=1
set @group_no=substring(@group_no,7,len(@group_no))
end
if(@group_no!='')
set @flag=0
return @flag
end
go--调用:
select * from [tb] where dbo.ps(group_no)=1
--结果:
group_no
------------------------
[4535][4353]
[4535][4353][4535][4353]
[4535][4353][4535][4353]
go
CREATE TABLE Test
(
seqno INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
Item NVARCHAR(360) NOT NULL DEFAULT('[0000]')
)
SELECT * FROM Test
INSERT INTO Test(Item) VALUES('[0123][1234][5678]')
INSERT INTO Test(Item) VALUES('[0123][4568][5678]')
INSERT INTO Test(Item) VALUES('[0123][4586][5678]')
INSERT INTO Test(Item) VALUES('[0123][4875][5678]')
INSERT INTO Test(Item) VALUES('[0123][0][5678]')
INSERT INTO Test(Item) VALUES('[0123][5896][5678]')DECLARE @IfVar NVARCHAR(300),@NMax INT ,@Pos INT
SELECT @IfVar='',@NMax=3,@Pos=1
WHILE @Pos <= @NMax
BEGIN
SELECT @IfVar=@IfVar+'/[[0-9][0-9][0-9][0-9]/]'
SELECT @Pos=@Pos+1
END
--PRINT @IfVar
SELECT * FROM Test WHERE Item LIKE @IfVar ESCAPE '/'
--SELECT * FROM Test