SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 IsInteger - replacement for IsNumeric
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/13/2005 :  09:01:46  Show Profile  Reply with Quote
Been meaning to post this for a while. It does a very limited job of only allowing [0-9], but could be extended to allow negative numbers, or numeric values that are suitable for numeric types other than INT, but avoiding the pitfalls of IsNumeric() which might allow through data not suitable for some of the numeric datatypes

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[kk_fn_UTIL_IsINT]') AND xtype IN (N'FN', N'IF', N'TF'))
	DROP FUNCTION dbo.kk_fn_UTIL_IsINT
GO
CREATE FUNCTION dbo.kk_fn_UTIL_IsINT
(
	-- String to be tested - Must only contain [0-9], any spaces are trimmed
	@strINT		varchar(8000)
)
RETURNS int	-- NULL = Bad INT encountered, else cleanedup INT returned
/* WITH ENCRYPTION */
AS
/*
 * kk_fn_UTIL_IsINT	Check that a String is a valid INT
 *			SELECT dbo.kk_fn_UTIL_IsINT(MyINTColumn)
 *			IF dbo.kk_fn_UTIL_IsINT(MyINTColumn) IS NULL ... Bad INT
 *
 * Returns:
 *
 *	int value	Valid integer
 *	NULL		Bad parameter passed
 *
 * HISTORY:
 *
 * 30-Sep-2005 Started
 */
BEGIN

DECLARE	@intValue	int

	SELECT	@strINT = LTRIM(RTRIM(@strINT)),
		@intValue = CASE WHEN @strINT NOT LIKE '%[^0-9]%'
			THEN CONVERT(int, @strINT)
			ELSE NULL
			END
	RETURN @intValue

/** TEST RIG

SELECT	dbo.kk_fn_UTIL_IsINT('123'),	IsNumeric('123')
SELECT	dbo.kk_fn_UTIL_IsINT(' 123 '),	IsNumeric(' 123 ')
SELECT	dbo.kk_fn_UTIL_IsINT('123.'),	IsNumeric('123.')
SELECT	dbo.kk_fn_UTIL_IsINT('123e2'),	IsNumeric('123e2')
SELECT	dbo.kk_fn_UTIL_IsINT('XYZ'),	IsNumeric('XYZ')
SELECT	dbo.kk_fn_UTIL_IsINT('-123'),	IsNumeric('-123')
SELECT	dbo.kk_fn_UTIL_IsINT('-'),	IsNumeric('-')

**/
--==================== kk_fn_UTIL_IsINT ====================--
END
GO

Kristen

SamC
White Water Yakist

USA
3459 Posts

Posted - 12/13/2005 :  09:27:38  Show Profile  Reply with Quote
Cool!

Post_Count = Post_Count + 1
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 12/13/2005 :  11:19:34  Show Profile  Reply with Quote
I think the result of your function will be collation-dependent for this:

SELECT	dbo.kk_fn_UTIL_IsINT('¹²³'), IsNumeric('¹²³')


Specifically, it will depend on whether the collation used in the comparison is a SQL or Windows one. Non-bin Windows collations will treat these characters as collating between 0 and 9.

If you want a laugh, try this:

SELECT n, NCHAR(n)
FROM Numbers
WHERE n BETWEEN 32 AND 65535
  AND NCHAR(n) NOT LIKE '%[^0-9]%'

which obviously () isn't the same as

SELECT n, NCHAR(n)
FROM Numbers
WHERE n BETWEEN 32 AND 65535
  AND NCHAR(n) LIKE '[0-9]'

(works best with an output font like Arial Unicode)

Edited by - Arnold Fribble on 12/13/2005 12:02:50
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5924 Posts

Posted - 12/13/2005 :  12:43:49  Show Profile  Reply with Quote
anyone got a simple algorithm for limiting a numeric string to valid ints?
ie: -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647)

because you know this will error out:
SELECT dbo.kk_fn_UTIL_IsINT('9999999999999999999999999999999999')



Be One with the Optimizer
TG
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 12/13/2005 :  12:48:21  Show Profile  Reply with Quote
I'd trim it, grab the minus sign, check the length, check the characters, cast it to bigint and check it's inside -2^31 -- 2^31-1, then cast it to int.
If your target type were bigint, then you'd have to use a NUMERIC type as an intermediate.

What a shame they didn't add that second argument to ISNUMERIC in SQL Server 2005!

Edited by - Arnold Fribble on 12/13/2005 12:53:13
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/13/2005 :  14:28:38  Show Profile  Reply with Quote
"I think the result of your function will be collation-dependent"

Thanks Arnold "Eagle-eye" Fribble

Presumably a suitable fix would be:

@intValue = CASE WHEN @strINT NOT LIKE '%[^0123456789]%'

Kristen
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 12/13/2005 :  15:18:20  Show Profile  Reply with Quote
quote:

Presumably a suitable fix would be:

@intValue = CASE WHEN @strINT NOT LIKE '%[^0123456789]%'



Ah, now that's dependent on the case-sensitivity of the collation!

@intValue = CASE WHEN @strINT NOT LIKE '%[^0-9]%' COLLATE Latin1_General_bin
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/13/2005 :  18:33:16  Show Profile  Reply with Quote
"Ah, now that's dependent on the case-sensitivity of the collation!"

Blimey! The digits are Case Sensitive?

Using "COLLATE Latin1_General_bin" is a fine idea, thanks.

Kristen
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 12/14/2005 :  04:46:37  Show Profile  Reply with Quote
"Blimey! The digits are Case Sensitive?"
Yes, for Windows collations (or comparisons of nvarchar, etc.). They're accent sensitive too, though there are no examples of that in CP1252. Try this:


SELECT n, NCHAR(n)
FROM Numbers
WHERE n BETWEEN 32 AND 65535
  AND NCHAR(n) COLLATE Latin1_General_CS_AS = '1'

SELECT n, NCHAR(n)
FROM Numbers
WHERE n BETWEEN 32 AND 65535
  AND NCHAR(n) COLLATE Latin1_General_CI_AS = '1'

SELECT n, NCHAR(n)
FROM Numbers
WHERE n BETWEEN 32 AND 65535
  AND NCHAR(n) COLLATE Latin1_General_CS_AI = '1'

SELECT n, NCHAR(n)
FROM Numbers
WHERE n BETWEEN 32 AND 65535
  AND NCHAR(n) COLLATE Latin1_General_CI_AI = '1'


Edited by - Arnold Fribble on 12/14/2005 04:48:38
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 12/14/2005 :  04:55:48  Show Profile  Reply with Quote
Did I mention that there are some real oddities in ISNUMERIC on unicode characters. This one always cracks me up:

SELECT NCHAR(8734), CAST(NCHAR(8734) AS float)

Result: 8.0
Unfortunately, NCHAR(8734) (U+221E) is the infinity symbol.
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 12/14/2005 :  06:10:09  Show Profile  Reply with Quote
quote:
Originally posted by Arnold Fribble

Did I mention that there are some real oddities in ISNUMERIC on unicode characters. This one always cracks me up:

SELECT NCHAR(8734), CAST(NCHAR(8734) AS float)

Result: 8.0
Unfortunately, NCHAR(8734) (U+221E) is the infinity symbol.




Brilliant!
*LOL*

rockmoose
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 12/14/2005 :  06:21:31  Show Profile  Reply with Quote
"Did I mention that there are some real oddities in ISNUMERIC on unicode characters."

Ah, apparently, I did, many years ago:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=13234
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 12/14/2005 :  06:28:36  Show Profile  Visit ditch's Homepage  Reply with Quote
>>SELECT NCHAR(8734), CAST(NCHAR(8734) AS float)

ha! ha!

It returns a float value of 8.0 in 2000,
but it returns
Error converting data type nvarchar to float
in 2005.

Just thought I'd point that out

Duane.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/14/2005 :  07:28:43  Show Profile  Reply with Quote
"Result: 8.0
Unfortunately, NCHAR(8734) (U+221E) is the infinity symbol.
"

Presumably they've changed it to return 9.0 in SQL 2k5?

Kristen
Go to Top of Page

henrik staun poulsen
Starting Member

4 Posts

Posted - 09/03/2008 :  01:56:40  Show Profile  Visit henrik staun poulsen's Homepage  Reply with Quote
Kristen,

We have found that a lot of digits will cause an integer overflow.

So to fix that, we do

SELECT @strINT = LTRIM(RTRIM(@strINT)),
@intValue = CASE WHEN @strINT NOT LIKE '%[^0-9]%'
COLLATE Latin1_General_bin
AND LEN(@strINT) <= 10
THEN CONVERT(int, @strINT)
ELSE NULL
END
RETURN @intValue

It still does not check for negative values, but your function is a great help. Thank you for sharing.

Best regards
Henrik Staun Poulsen
Go to Top of Page

henrik staun poulsen
Starting Member

4 Posts

Posted - 09/04/2008 :  05:12:11  Show Profile  Visit henrik staun poulsen's Homepage  Reply with Quote
Kristen,

Now we do like this:


DECLARE @IntegerValue INT, @BigInt BIGINT

SELECT @IntegerString = LTRIM(RTRIM(@IntegerString))

-- remove everything right of a decimal point
IF CHARINDEX('.', @IntegerString) > 0 SELECT @IntegerString = LEFT(@IntegerString, CHARINDEX('.', @IntegerString)-1)

SELECT @BigInt = CASE WHEN LEFT(@IntegerString,1) LIKE '[-0-9+]' COLLATE Latin1_General_bin
AND PATINDEX('%[^0-9]%', SUBSTRING(@IntegerString, 2, 18) COLLATE Latin1_General_bin) = 0
AND LEN(@IntegerString) <= 11
THEN CONVERT(BIGINT, @IntegerString)
ELSE NULL
END

SELECT @IntegerValue = @BigInt
WHERE @BigInt >= -2147483648 AND @BigInt <= 2147483647

RETURN @IntegerValue

with this as a test:
SELECT utl.ConvertToInteger('123'), IsNumeric('123')
SELECT utl.ConvertToInteger(' 123 '), IsNumeric(' 123 ')
SELECT utl.ConvertToInteger('123.'), IsNumeric('123.')
SELECT utl.ConvertToInteger('123.0'), IsNumeric('123.0')
SELECT utl.ConvertToInteger('123e2'), IsNumeric('123e2') -- not an int
SELECT utl.ConvertToInteger('XYZ'), IsNumeric('XYZ') -- not an int
SELECT utl.ConvertToInteger('-123'), IsNumeric('-123')
SELECT utl.ConvertToInteger('-'), IsNumeric('-') -- converts to zero, is this what we want?
SELECT utl.ConvertToInteger('-999999999'), IsNumeric('-999999999') -- not too big
SELECT utl.ConvertToInteger('-2147483648'), IsNumeric('-2147483648') -- not too big
SELECT utl.ConvertToInteger('-9999999999'), IsNumeric('-9999999999') -- too big
SELECT utl.ConvertToInteger('2147483647'), IsNumeric('2147483647') -- not too big
SELECT utl.ConvertToInteger('2147483648'), IsNumeric('2147483648') -- too big
SELECT utl.ConvertToInteger('9999999999'), IsNumeric('9999999999') -- too big for integer, but still 9 digits
SELECT utl.ConvertToInteger('99999999999'), IsNumeric('99999999999') -- too big
SELECT utl.ConvertToInteger('999999999999999999999999999999'), IsNumeric('999999999999999999999999999999') -- too big
SELECT utl.ConvertToInteger('12345678901234567890hsp'), IsNumeric('12345678901234567890hsp') -- not an int
SELECT utl.ConvertToInteger('1 hsp'), IsNumeric('1 hsp') -- not an int


Best regards,
Henrik

Edited by - henrik staun poulsen on 09/04/2008 05:13:04
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000