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
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?
ISNUMERIC(Data) AS [IsNumeric],
ISNUMERIC(Data + 'e0') AS IsReallyNumeric