IsInteger - replacement for IsNumeric

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

IsInteger - replacement for IsNumeric

Author

Topic

Kristen
Test

22859 Posts

Posted - 2005-12-13 : 09:01:46

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

3467 Posts

Posted - 2005-12-13 : 09:27:38

Cool!

Post_Count = Post_Count + 1

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-12-13 : 11:19:34

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)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-13 : 12:43:49

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

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-12-13 : 12:48:21

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!

Kristen
Test

22859 Posts

Posted - 2005-12-13 : 14:28:38

"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

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-12-13 : 15:18:20

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

Kristen
Test

22859 Posts

Posted - 2005-12-13 : 18:33:16

"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

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-12-14 : 04:46:37

"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'

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-12-14 : 04:55:48

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.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-12-14 : 06:10:09

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

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-12-14 : 06:21:31

"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

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-12-14 : 06:28:36

>>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.

Kristen
Test

22859 Posts

Posted - 2005-12-14 : 07:28:43

"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

henrik staun poulsen
Starting Member

4 Posts

Posted - 2008-09-03 : 01:56:40

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

henrik staun poulsen
Starting Member

4 Posts

Posted - 2008-09-04 : 05:12:11

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources