参考: create table tb(id nvarchar(10)) insert into tb select '10001' insert into tb select '10303a' insert into tb select '20020' insert into tb select '90034' insert into tb select '100042' insert into tb select '水利40101-1' insert into tb select '水利50006' insert into tb select '省水9-4' insert into tb select '安C8-357' go select substring(id,n,c-n) from( select *,(select min(number) from master..spt_values where type='p' and number between b.n and 100 and substring(b.id,number,1) not between '0' and '9')c from( select id, (select min(number) from master..spt_values where type='p' and number between 1 and 100 and substring(a.id,number,1) between '0' and '9')n from tb a )b)t /*---------- 10001 10303 20020 90034 100042 40101 50006 9 8(9 行受影响)*/ go drop table tb
if object_id('tb') is not null drop table tb go create table tb ( id int identity(1,1), name varchar(30) ) go insert into tb select '鸦帆布鞋844042原价229元' union select '娃哈哈321原价451' go if object_id('uf_tb','FN') is not null drop function uf_tb go create function uf_tb(@name varchar(100)) returns varchar(10) as begin declare @i int,@substr varchar(100),@f bit set @i=1 set @f='false' set @substr='' while @i<len(@name) begin if (isnumeric(substring(@name,@i,1))=1) begin set @substr=@substr+substring(@name,@i,1) set @f='true' end else if @f='true' break; set @i=@i+1 end return @substr end goselect id,name,现价=dbo.uf_tb(name) from tb /* id name 现价 ----------- ------------------------------ ---------- 1 鸦帆布鞋844042原价229元 844042 2 娃哈哈321原价451 321(2 行受影响) */
create table tb(id nvarchar(10))
insert into tb select '10001'
insert into tb select '10303a'
insert into tb select '20020'
insert into tb select '90034'
insert into tb select '100042'
insert into tb select '水利40101-1'
insert into tb select '水利50006'
insert into tb select '省水9-4'
insert into tb select '安C8-357'
go
select substring(id,n,c-n) from(
select *,(select min(number) from master..spt_values where type='p' and number between b.n and 100 and substring(b.id,number,1) not between '0' and '9')c
from(
select id,
(select min(number) from master..spt_values where type='p' and number between 1 and 100 and substring(a.id,number,1) between '0' and '9')n
from tb a
)b)t
/*----------
10001
10303
20020
90034
100042
40101
50006
9
8(9 行受影响)*/
go
drop table tb
if object_id('tb') is not null
drop table tb
go
create table tb
(
id int identity(1,1),
name varchar(30)
)
go
insert into tb
select '鸦帆布鞋844042原价229元' union
select '娃哈哈321原价451'
go
if object_id('uf_tb','FN') is not null
drop function uf_tb
go
create function uf_tb(@name varchar(100))
returns varchar(10)
as
begin
declare @i int,@substr varchar(100),@f bit
set @i=1
set @f='false'
set @substr=''
while @i<len(@name)
begin
if (isnumeric(substring(@name,@i,1))=1)
begin
set @substr=@substr+substring(@name,@i,1)
set @f='true'
end
else if @f='true'
break;
set @i=@i+1
end
return @substr
end
goselect id,name,现价=dbo.uf_tb(name) from tb
/*
id name 现价
----------- ------------------------------ ----------
1 鸦帆布鞋844042原价229元 844042
2 娃哈哈321原价451 321(2 行受影响)
*/