Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Microsoft SQL Server: Programming FAQ

T-SQL Hints and Tips

IsNumeric, IsInt, IsNumber
Posted: 2 Oct 06 (Edited 3 Oct 06)

Occasionally, it's necessary to convert character data (char, nchar, varchar, nvarchar) to a number.  Before doing so, it is best to make sure that the value can be converted to a number.

IsNumeric would be the obvious choice, but has some problems because it allows for unexpected characters during the conversion.  For example, the following strings will return true from the IsNumeric function.

$12.09
1.4e3
2d4

Technically, all of these are numbers.

Select Convert(Money, '$12.09')
Select Convert(Float, '1.4e3')
Select Convert(Float, '2d4')

Often times, we don't want to allow the dollar sign or scientific notation.  

To make sure a value can be converted to an integer, you can append .0e0 to the end of the string before checking.  If the original value had a decimal point, then adding another decimal point will cause the value to not be numeric.  Similarly, adding e0 to a value that is already expressed in scientific notation will cause IsNumeric to return false.

Consider the following values:


Original   + '.0e0'    + 'e0'
--------   ----------  --------
$12.09     $12.09.0e0  $12.09e0
1.4e3      1.4e3.0e0   1.4e3e0
2d4        2d4.0e0     2d4e0
3.7        3.7.0e0     3.7e0
412        412.0e0     412e0


Notice how the second column will only evaluate to true for the value that can be converted to an integer.  Als notice how the 3rd column will only evaluate to true for the last 2 values.

You can create your own User Defined Function to check for integers, like so:

CODE

CREATE Function dbo.IsInteger(@Value VarChar(18))
Returns Bit
As
Begin
  
  Return IsNull(
     (Select Case When CharIndex('.', @Value) > 0
                  Then Case When Convert(int, ParseName(@Value, 1)) <> 0
                            Then 0
                            Else 1
                            End
                  Else 1
                  End
      Where IsNumeric(@Value + 'e0') = 1), 0)

End

To use this new function:


Select Convert(int, field)
From   Table
Where  dbo.IsInteger(field) = 1


If you want to allow fractional numbers, then you can add e0 to the isnumeric test.

Select IsNumeric('$12.09' + 'e0')
Select IsNumeric('1.4e3' + 'e0')
Select IsNumeric('2d4' + 'e0')
Select IsNumeric('3.7' + 'e0')

Again, you can create a User Defined Function to test this.

CODE

Create Function IsNumber(@Value VarChar(18))
Returns Bit
As
Begin
  Return (Select IsNumeric(@Value + 'e0'))
End

After creating the User Defined Functions, you can test it with the following code.

CODE

Declare @Temp Table(Data VarChar(18))

Insert Into @Temp Values('$12.09')
Insert Into @Temp Values('1.4e3')
Insert Into @Temp Values('2d4')
Insert Into @Temp Values('3.7')
Insert Into @Temp Values('412')

Select Data,
       IsNumeric(Data) As [IsNumeric],
       dbo.IsInteger(Data) As IsInteger,
       dbo.IsNumber(Data) As IsNumber
From   @Temp

Special thanks to SQLDenis for helping me work out the exceptions in the IsInteger function.

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close