数据库截取字段并转换成int型函数
USE [IAR_Factory_509]
GO
/****** 对象: UserDefinedFunction [dbo].[GET_STRFILE1] 脚本日期: 09/13/2012 13:24:12 ******/ www.2cto.com
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 ) www.2cto.com
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 www.2cto.com
else
select @ret=0
end
return @ret
end