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
Go to Top of Page

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)
Go to Top of Page

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
Go to Top of Page

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!
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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'

Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -