Thursday, August 23, 2007

Summer SQL Teaser #13 Numeric

Hi and welcome to another fascinating SQL summer teaser. Summer it is except inPrinceton 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 the teaser will be posted on a Thursday 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

Scott Whigham said...

Fun :)

well, hate to say it but your blog software kinda made a typo when it published the VALUES() statements so you might want to double check.

I've never seen the 2d4 syntax in SQL - neat to learn something new :)

One logic flaw though in your "IsReallyNumeric" algorithm: ISNUMERIC(Data + 'e0') incorrectly marks 1.4e35 as 0 instead of 1. 1.4e35 is really a number when not a set of characters; appending e0 at the end, though, makes it an invalid format (but the "base" is still a number).

Thanks for sharing :)

SQL said...

Thanks, what I meant by "IsReallyNumeric" is basically a number with or without decimals, no scientific notation

Anonymous said...

How about '.'? This has caused me a lot of pain in importing data.