create table t
(
x varchar(100)
)
insert into t
select 'd458d' union all
select '3424' union all
select 'wikdd2' union all
select '323i'
create function aa(@s varchar(100))
returns varchar(100)
as
begin
declare @len INT
declare @i int
set @i=0
set @len=len(@s)
while @i<=@len+1
begin
IF ascii(upper(substring(@s,@i,1)))>=65 and ascii(upper(substring(@s,@i,1)))<=90
begin
set @s=ltrim(replace(@s,substring(@s,@i,1),''))
end
set @i=@i+1
end
return @s
end
--創建測試環境
Create Table TEST(xC Varchar(100))
--插入數據
Insert Into TEST Select '123P'
Union All Select '34L'
Union All Select 'M230'
Union All Select 'H98G'
GO
--創建函數
Create Function F_GetNumeric(@xC Varchar(100))
Returns Varchar(100)
As
Begin
Declare @NewxC Varchar(100)
Select @NewxC = ''
While(Len(@xC) > 0)
Begin
If (ASCII(Left(@xC, 1)) Between 48 And 57)
Select @NewxC = @NewxC + Left(@xC, 1)
Select @xC = Stuff(@xC, 1, 1, '')
End
Return @NewxC
End
GO
--測試
Select dbo.F_GetNumeric(xC) As xC From TEST
GO
--刪除測試環境
Drop Table TEST
Drop Function F_GetNumeric
--結果
/*
xC
123
34
230
98
*/
create table tab(xc varchar(10))
insert tab
select '123P'
union all
select '34L'
union all
select 'M230'
union all
select 'H98G'
--要求得到其中的数字部分
select patindex('%[0-9]%',xc),len(xc),len(xc)-patindex('%[0-9]%',xc),
right(xc,len(xc)-patindex('%[0-9]%',xc)+1),
patindex('%[A-Z]%',right(xc,len(xc)-patindex('%[0-9]%',xc)+1)),
substring(xc,patindex('%[0-9]%',xc),(case when patindex('%[A-Z]%',right(xc,len(xc)-patindex('%[0-9]%',xc)+1))=0 then len(right(xc,len(xc)-patindex('%[0-9]%',xc)+1)) else patindex('%[A-Z]%',right(xc,len(xc)-patindex('%[0-9]%',xc)+1))-1 end))
from tab
drop table tab