--
PRINT 'Create function kk_fn_UTIL_IsMoney'
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[kk_fn_UTIL_IsMoney]') 
		AND xtype IN (N'FN', N'IF', N'TF'))
	DROP FUNCTION dbo.kk_fn_UTIL_IsMoney
GO

CREATE FUNCTION dbo.kk_fn_UTIL_IsMoney
(
	-- String to be tested - Must only contain [0-9], decimal point, leading +/- or surrounding brackets
	@strMONEY		varchar(8000)
)
RETURNS money	-- NULL = Bad value encountered, else cleanedup MONEY returned
/* WITH ENCRYPTION */
AS
/*
 * kk_fn_UTIL_IsMoney	Check that a String is a valid MONEY
 *			SELECT dbo.kk_fn_UTIL_IsMoney(MyMoneyColumn)
 *			IF dbo.kk_fn_UTIL_IsMoney(MyMoneyColumn) IS NULL ... Bad MONEY
 *
 * For latest version see:
 * 	http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90279
 *
 * Returns:
 *
 *	money value
 *	NULL		Bad parameter passed
 *
 * HISTORY:
 *
 * 01-Oct-2007 Kristen  Started
 */
BEGIN

DECLARE	@mnyValue	money,
	@strNegative	varchar(1)

	SELECT	@strMONEY = REPLACE(LTRIM(RTRIM(@strMONEY)), ',', ''),	--Trim and remove thousands-separators
		@strMONEY = CASE WHEN @strMONEY LIKE '(%)' THEN '-' + SUBSTRING(@strMONEY, 2, DATALENGTH(@strMONEY)-2)
					ELSE @strMONEY
					END,
		@strNegative = CASE WHEN @strMONEY LIKE '-%' THEN '-'
					ELSE ''
					END,
		@strMONEY = CASE WHEN @strNegative = '-' THEN SUBSTRING(@strMONEY, 2, 999)
					ELSE @strMONEY
					END,
		@mnyValue = CASE 
			WHEN @strMONEY LIKE '%[^0-9.]%' OR @strMONEY LIKE '%.%.%'
				THEN NULL
			WHEN (@strMONEY LIKE '%.[0-9][0-9][0-9][0-9]' AND  DATALENGTH(@strMONEY) >= 21)
			  OR (@strMONEY LIKE '%.[0-9][0-9][0-9]' AND  DATALENGTH(@strMONEY) >= 20)
			  OR (@strMONEY LIKE '%.[0-9][0-9]' AND  DATALENGTH(@strMONEY) >= 19)
			  OR (@strMONEY LIKE '%.[0-9]' AND  DATALENGTH(@strMONEY) >= 18)
			  OR (@strMONEY NOT LIKE '%.%' AND  DATALENGTH(@strMONEY) >= 16)
				THEN NULL
			WHEN CONVERT(bigint, LEFT(@strMoney, DATALENGTH(@strMoney) - CHARINDEX('.', REVERSE(@strMoney)))) 
					NOT BETWEEN -922337203685477 AND 922337203685477
				THEN NULL
			WHEN @strMONEY NOT LIKE '%[^0-9.]%' AND @strMONEY NOT LIKE '%.%.%'
				THEN CONVERT(money, @strNegative + @strMONEY)
			ELSE NULL
			END
	RETURN @mnyValue

/** TEST RIG

DECLARE @TestData TABLE
(
	strMoney	varchar(30)
)
--
INSERT INTO @TestData(strMoney)
SELECT	'123' UNION ALL
SELECT	' 123 ' UNION ALL
SELECT	'123.' UNION ALL
SELECT	'123e2' UNION ALL
SELECT	'XYZ' UNION ALL
SELECT	'123.4' UNION ALL
SELECT	'123.45' UNION ALL
SELECT	'123.456' UNION ALL
SELECT	'123.4567' UNION ALL
SELECT	'123.45678' UNION ALL
SELECT	'123.4567.8' UNION ALL
SELECT	'-123.4567' UNION ALL
SELECT	'(123.4567)' UNION ALL
SELECT	'9,123.4567' UNION ALL
SELECT	'(9,123.4567)' UNION ALL
SELECT	'-9,123.4567' UNION ALL
SELECT	'-922337203685476.9999' UNION ALL
SELECT	'-922337203685477.5808' UNION ALL
SELECT	'-922337203685478' UNION ALL
SELECT	'922337203685477.5807' UNION ALL
SELECT	'1922337203685477.5807' UNION ALL
SELECT	'1922337203685477.580' UNION ALL
SELECT	'1922337203685477.58' UNION ALL
SELECT	'1922337203685477.5' UNION ALL
SELECT	'1922337203685477.' UNION ALL
SELECT	'1922337203685477' UNION ALL
SELECT	'11111111111922337203685477'
--
SELECT	[MaxLen]=MAX(DATALENGTH(strMoney))
FROM	@TestData
SELECT	']'+strMoney+'[', IsNumeric(strMoney), dbo.kk_fn_UTIL_IsMoney(strMoney)
FROM	@TestData

-- This will fail, decimal part too large, but Money value is valid.  Only applies to .5809 through .9999
SELECT	'-922337203685477.5809', IsNumeric('-922337203685477.5809'), dbo.kk_fn_UTIL_IsMoney('-922337203685477.5809')

SELECT	CONVERT(money, CONVERT(float, '123e2'))	-- Valid
SELECT	CONVERT(money, '123e2')	-- Invalid

**/
--==================== kk_fn_UTIL_IsMoney ====================--
END
GO
PRINT 'Create function kk_fn_UTIL_IsMoney DONE'
GO
--