one: declare @s varchar(20) declare @i varchar(20) set @i='' set @s='新會員必須購買350元產品' while PATINDEX ('%[0-9]%', @s)>0 begin set @i=@i+substring(@s,PATINDEX ('%[0-9]%', @s),1) set @s=stuff(@s,1,PATINDEX ('%[0-9]%', @s),'') end select @i -- 300 two: declare @a table(id int identity(1,1),a varchar(100)) insert @a select '新會員必須購買350元產品' union all select '新店首次定貨必須滿20000元'
select left(right(a,len(a)-patindex('%[0-9]%',a)+1),len(right(a,len(a)-patindex('%[0-9]%',a)+1))-1) from @a
create function fn_GetNum(@s varchar(8000)) returns varchar(8000) as begin select @s = stuff(stuff(@s, 1, patindex('%[0-9, .]%', @s) - 1, ''), patindex('%[^0-9, .]%', stuff(@s, 1, patindex('%[0-9, .]%', @s) - 1, '')), len(@s), '') return @s end
declare @t table(s varchar(8000)) insert @t select 'aaa11112bbb' union all select 'ccc212sss' union all select 'sss21a' select dbo.fn_GetNum(s) as result from @t
select substring(s,patindex('%[^0-9][0-9]%',s)+1,patindex('%[0-9][^0-9]%',s)-patindex('%[^0-9][0-9]%',s)) from @t
/*功能:獲取字符串中的字母*/ CREATE FUNCTION dbo.F_Get_STR (@S VARCHAR(100)) RETURNS VARCHAR(100) AS BEGIN WHILE PATINDEX('%[^a-z]%',@S)>0 BEGIN set @s=stuff(@s,patindex('%[^a-z]%',@s),1,'') END RETURN @S END GO --測試 select dbo.F_Get_STR('測試ABC123ABC') GO /* 功能:獲取字符串中的數字 */ create function dbo.F_Get_Number (@S varchar(100)) returns int AS begin while PATINDEX('%[^0-9]%',@S)>0 begin set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'') end return cast(@S as int) end --測試 ---select dbo.F_Get_Number('測試AB3C123AB5C') GO
