|
数据库截取字段并转换成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 |