Discuz!NT官方社区

declare @str varchar(100)
declare @i int,@str1 varchar(10)
set @str='sddfd123fddfd56fddf78'    --要分解的字符串
declare @tb table(num varchar(20))    --定义保存结果的表
set @i=patindex('%[^0-9]%',@str)
while @i>0
begin
    select @str1=left(@str,@i-1)
        ,@str=substring(@str,@i,100)
        ,@i=patindex('%[0-9]%',@str)
        ,@str=substring(@str,@i,100)
        ,@i=patindex('%[^0-9]%',@str)
    insert into @tb values(@str1)
end
if @str<>'' insert into @tb values(@str)
--显示结果
select * from @tb where num <> ''

--取字母(假设为a-z)
declare @str varchar(100)
declare @i int,@str1 varchar(10)
set @str='sddfd123fddfd56fddf78'+','    --要分解的字符串
declare @tb table(english varchar(20))    --定义保存结果的表
set @i=patindex('%[^A-Za-z,]%',@str)
while @i>0
begin
    select @str1=left(@str,@i-1)
        ,@str=substring(@str,@i,100)
        ,@i=patindex('%[A-Za-z,]%',@str)
        ,@str=substring(@str,@i,100)
        ,@i=patindex('%[^A-Za-z,]%',@str)
    insert into @tb values(@str1)
end
if @str<>'' insert into @tb values(@str)
--显示结果
select * from @tb where english <> ','

IF OBJECT_ID('dbo.c1') IS NOT NULL
  DROP TABLE dbo.c1;
GO
create  table  c1(str  varchar(100) NOT NULL PRIMARY KEY)
insert  into  c1  values('12323sfs2342sfas342sas2sfa44')
insert  into  c1  values('asd123sdf34334sdf43432sdf342a')
insert  into  c1  values('asf234sfs23423434sdfa342423')
insert  into  c1  values('123213sdfdfwke434322lkkjl')
go
declare @str varchar(100)
declare @i int,@j int
set @str=''
set @i=0
while @i<47000
begin
select @str=@str+(case
when cast(rand()*2 as int)=1 then cast(cast(cast(rand()*26+97 as int) as binary(1)) as varchar)
else cast(cast(rand()*10 as int) as varchar) end
) from  where n<20
insert into c1 values(@str)
set @str=''
set @i=@i+1
end
select * from c1