declare @t table(name varchar(10))
insert @t
select '00x1 ' union all
select '0002 ' union all
select '00003 '
select convert(int,stuff(name,1,patindex( '%[^0-9]% ',name), ' ')) as name
from @t
+++++++++++++++++++++++++
输出结果:
1
2
3
+++++++++++++++++++++++++++++++++++++++++++++++++++++++
为什么字符串一加长就出错。。。。。。
declare @t table(name varchar(10))
insert @t
select '00000000000000x1 ' union all
select '0002 ' union all
select '00003 '
select convert(int,stuff(name,1,patindex( '%[^0-9]% ',name), ' ')) as name
from @t
------解决方案--------------------
create function fun_xx()
returns @a table(name varchar(100))
as
begin
declare @t table(name varchar(100))
insert @t
select '00000000000000x1 ' union all
select '0002 ' union all
select '00003 '
insert @a select convert(int,stuff(name,1,patindex( '%[^0-9]% ',name), ' ')) as name
from @t
return
end
go
select * from dbo.fun_xx()
------解决方案--------------------
create function fun_xx(@RegionParameter nvarchar(30) )
returns @a table(name varchar(100))
as
begin
declare @t table(name varchar(100))
insert @t
select @RegionParameter
insert @a select convert(int,stuff(name,1,patindex( '%[^0-9]% ',name), ' ')) as name
from @t
return
end
GO
--你掉了GO
--這裡是調用
select * from dbo.fun_xx( '000000000000000000000001 ')