数据库截取字段 转换成int型函数

USE [IAR_Factory_509]
GO
/****** 对象:  UserDefinedFunction [dbo].[GET_STRFILE1]    脚本日期: 09/13/2012 13:24:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GET_STRFILE1](@a VARCHAR(50),@num int)
RETURNS VARCHAR(50)
AS
BEGIN
  DECLARE @SQL NVARCHAR(500)
  declare @ret int
declare @isNum int
declare @sec varchar(50) 
 if(@a!='')
if(@num=0)
 select @SQL=PATINDEX('%[0-9]%', @a)
  else
 select @SQL=PATINDEX('%]%', @a)
 if(@SQL>0)
begin
  select @sec=SUBSTRING(@a, PATINDEX('%]%', @a)+1, LEN(@a) - PATINDEX('%]%', @a) + 1 )
  select @isNum=isNumeric(@sec)
   if(@isNum=1)
     begin
     select @ret=cast(@sec as int)
     end
   else  
    begin
     select @ret=cast(SUBSTRING(@sec, PATINDEX('%[0-9]%', @sec), PATINDEX('%[^0-9%]', @sec)- 1 ) as int)
    end
 end
 else
  begin
     select @isNum=isNumeric(SUBSTRING(@a, PATINDEX('%]%', @a)+1, LEN(@a) - PATINDEX('%]%', @a) + 1 ))
   if(@isNum=1)
     begin
     select @ret=cast(SUBSTRING(@a, PATINDEX('%]%', @a)+1, LEN(@a) - PATINDEX('%]%', @a) + 1 ) as int)
     end
   else
 select @ret=0
  end
return @ret
end