1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83



-- ufn_IsNumericEx ---
IF EXISTS (SELECT * FROM   sysobjects WHERE  name = N'ufn_IsNumericEx')
	DROP FUNCTION ufn_IsNumericEx
GO
/* 
	Checks if string can be converted into a number
 	Parameters:
		@String  - The character expression to check
		@NumericType - numeric data type: int, numeric, float, real, money, etc.
	 Returns: 1 - if string can be converted to the type specified 	
			  0 - if string cannot be converted to the type specified 	
			  NULL - if 2nd parameter is invalid
	Note: the function doesn't check if the numeric value is in the range for specified data type	
*/

CREATE FUNCTION ufn_IsNumericEx 
	(@String varchar(38),
	 @NumericType varchar(16) )
RETURNS int
AS
BEGIN

	-- If it's not numeric, no reason to continue
	IF ISNUMERIC(@String) = 0
		RETURN 0
	
	DECLARE @pos int, @IllegalChars varchar(16)
	--	@IllegalChars - The list of illegal characters. Possible meaningfull values '$de.,'
	SET @IllegalChars = CASE 
		WHEN @NumericType in ('bigint', 'int', 'smallint', 'tinyint') THEN '$de.,'
		WHEN @NumericType IN ('numeric', 'decimal') THEN '$de,'
		WHEN @NumericType IN ('float', 'real') THEN '$,'
		WHEN @NumericType = 'money' THEN 'de'
		ELSE NULL END	
	
	-- Return NULL if 2nd parameter is invalid
	IF @IllegalChars IS NULL 
		RETURN NULL

	SET @pos = 1

	-- Replace characters specified as illegal with the character 
	--		that is always illegal, 'z' for example 
	WHILE @pos <= LEN(@IllegalChars)
	BEGIN
		SET @String = REPLACE(@String, SUBSTRING(@IllegalChars, @pos, 1), 'z')
		SET @pos = @pos + 1
	END

	RETURN ISNUMERIC(@String)
END
GO

-- Sample of using ufn_IsNumericEx UDF
SET NOCOUNT ON
DECLARE @tbl TABLE ( StrVal varchar(38)) 

INSERT INTO @tbl VALUES ('$123,456.7890')
INSERT INTO @tbl VALUES ('-$123,456.7890')
INSERT INTO @tbl VALUES ('1234567890')
INSERT INTO @tbl VALUES ('-1234567890')
INSERT INTO @tbl VALUES ('123456.7890e23')
INSERT INTO @tbl VALUES ('123456.7890d23')
INSERT INTO @tbl VALUES ('123,456.7890')
INSERT INTO @tbl VALUES ('123456.7890')
INSERT INTO @tbl VALUES ('-123456.7890')
INSERT INTO @tbl VALUES ('123456.7890-')

SELECT StrVal, ISNUMERIC(StrVal) AS IsNumeric, 
		dbo.ufn_IsNumericEx(StrVal, 'int') AS IsInteger, 
		dbo.ufn_IsNumericEx(StrVal, 'decimal') AS IsDecimal, 
		dbo.ufn_IsNumericEx(StrVal, 'money') AS IsMoney, 
		dbo.ufn_IsNumericEx(StrVal, 'real') AS IsReal, 
			CASE WHEN dbo.ufn_IsNumericEx(StrVal, 'int') = 1 
					THEN CAST(StrVal AS int) ELSE 0 END AS IntegerValue,
			CASE WHEN dbo.ufn_IsNumericEx(StrVal, 'decimal') = 1 
					THEN CAST(StrVal AS decimal) ELSE 0 END AS DecimalValue,
			CASE WHEN dbo.ufn_IsNumericEx(StrVal, 'money') = 1 
					THEN CAST(StrVal AS money) ELSE 0 END AS MoneyValue,
			CASE WHEN dbo.ufn_IsNumericEx(StrVal, 'real') = 1 
					THEN CAST(StrVal AS real) ELSE 0 END AS RealValue
	FROM @tbl