SQL Server 字符串拆分指定字符(%)前的数字(整数)和字符

select pmat_id 編碼,pmat_name  原名稱,case len(dbo.getNumber(SUBSTRING(pmat_name,CHARINDEX('%',pmat_name)-2,2))) when 1 then rtrim(SUBSTRING(pmat_name,1,CHARINDEX('%',pmat_name)-2)) else rtrim(SUBSTRING(pmat_name,1,CHARINDEX('%',pmat_name)-3)) end as 將要更改成的名稱,

dbo.getNumber(SUBSTRING(pmat_name,CHARINDEX('%',pmat_name)-2,2))  as 截取的數量

 from pmat where pmat_id like 'sz%' and pmat_name like '%[%]' and pmat_name  not like '%.%'

 union all

 select pmat_id 編碼,pmat_name 原名稱, rtrim(SUBSTRING(pmat_name,1,CHARINDEX('(',pmat_name)-1))  as 將要更改成的名稱,

dbo.getNumber(SUBSTRING(pmat_name,CHARINDEX('%)',pmat_name)-2,2))  as 截取的數量

 from pmat where pmat_id like 'sz%' and pmat_name like '%[%][)]' and pmat_name  not like '%.%'

----------------------------------------------------------------------------------------------

create FUNCTION [dbo].[getNumber]

(

   @Name varchar(20)

)

--select @Name='500tt'

RETURNS bigint

AS

BEGIN  

       DECLARE @re_number int

while patindex('%[^0-9]%',@Name)>0

begin

set @Name=stuff(@Name,patindex('%[^0-9]%',@Name),1,'')

end

select @re_number=cast(@Name as bigint)

RETURN @re_number

END