THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

Summer SQL Teaser #12 Numeric

Hi and welcome to another fascinating SQL summer teaser.

Summer it is except in Princeton where it was 50 degrees this week.

There was no teaser last week because of a death in the family, I had to go to a wake and a funeral last week. That is why this teaser will be posted on a Thursday instead of a Friday this week ;-)

look at these values

$55.69
1.4e35
2d4
3.7
412

How many numeric values do you see? What do you think SQL Server's ISNUMERIC function will return for those values? Let's find out, run the following code

CREATE TABLE #Temp (Data varchar(18))


INSERT INTO #Temp VALUES('$55.69')
INSERT INTO #Temp VALUES(1.4e35')
INSERT INTO #Temp VALUES(2d4')
INSERT INTO #Temp VALUES(3.7')
INSERT INTO #Temp VALUES(412')
INSERT INTO #Temp VALUES(CHAR(9)) --tab


Now without running this try to guess which values will be 1 and which 0. I added a bonus ISNUMERIC(ColumnName + 'e0') function. What do you think that will do? Remember first try to guess and then run the code. Any surprises?

SELECT Data,
ISNUMERIC(Data) AS [IsNumeric],
ISNUMERIC(Data + 'e0') AS IsReallyNumeric
FROM #Temp

Published Thursday, August 23, 2007 1:41 PM by Denis Gobo
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Madhivanan said:

If the number is expressed in scientific format (eg 12d2, 12e2), ISNUMERIC treat that as number. However when you append e0 the number would be considered as nonnumeric, if it is already in a specific format. The same case applies when you use d0 in place of e0

August 24, 2007 6:03 AM
 

Patrick Callahan said:

--It's interesting to note which of the values can actually be converted to a numeric type:

--Try adding convert(numeric,data) to the select:

SELECT Data,

ISNUMERIC(Data) AS [IsNumeric],

ISNUMERIC(Data + 'e0') AS IsReallyNumeric,

Data + 'e0',

convert(numeric,data)

FROM #Temp

--If #temp contains any values that are not "really numeric", you get:

Msg 8114, Level 16, State 5, Line 12

Error converting data type varchar to numeric.

go figure...

So what is Isnumeric for again?

Next, try this:

SELECT Data,

ISNUMERIC(Data) AS [IsNumeric],

ISNUMERIC(Data + 'e0') AS IsReallyNumeric,

Data + 'e0',

convert(float,data)

FROM #Temp

Now which ones get "Error converting data type varchar to numeric."

how about convert(money,data)?

and cast (ascii(data) as int)

So for next time Denis, how do I write an expression that returns some kind of numeric type and does not produce an error message for all of the original data in #temp?

August 27, 2007 7:24 AM
 

Denis Gobo said:

Patrick,

Here are two functions which should help you out

use them with a case expression (case when function = 1 then convert else null end), you would have to write one for money, but in my opinion money should never be used and removed from SQL server because of rounding issues

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

Go

Create Function IsNumber(@Value VarChar(18))

Returns Bit

As

Begin

 Return (Select IsNumeric(@Value + 'e0'))

End

August 27, 2007 7:56 AM
 

Madhivanan said:

August 27, 2007 8:09 AM
 

George said:

Denis Gobo: "in my opinion money should never be used and removed from SQL server because of rounding issues"

Can you explain that one, perhaps with an example?

I've never come across any such issues!

September 3, 2007 7:59 AM
 

Hugo Kornelis said:

George: "Can you explain that one, perhaps with an example?"

Just copy and paste this code into SSMS and hit execute:

DECLARE @M1 money, @M2 money

DECLARE @N1 numeric(12,4), @N2 numeric(12,4);

DECLARE @F1 float, @F2 float;

SET @M1 = 12.3456;

SET @M2 = 78.9012;

SET @N1 = @M1;

SET @N2 = @M2;

SET @F1 = @M1;

SET @F2 = @M2;

SELECT (@M1 / @M2) * @M2;

SELECT (@N1 / @N2) * @N2;

SELECT (@F1 / @F2) * @F2;

Best, Hugo

September 4, 2007 3:10 AM
 

Denis Gobo said:

Here is another one from SQL Server MVP David Portas

DECLARE

@mon1 MONEY,

@mon2 MONEY,

@mon3 MONEY,

@mon4 MONEY,

@num1 DECIMAL(19,4),

@num2 DECIMAL(19,4),

@num3 DECIMAL(19,4),

@num4 DECIMAL(19,4)

SELECT

@mon1 = 100, @mon2 = 339, @mon3 = 10000,

@num1 = 100, @num2 = 339, @num3 = 10000

SET @mon4 = @mon1/@mon2*@mon3

SET @num4 = @num1/@num2*@num3

SELECT @mon4 AS money_result,

@num4 AS numeric_result

Result:

money_result numeric_result

--------------------- ---------------------

2949.0000 2949.8525

There you have it

September 4, 2007 10:09 AM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement