Select ... Where ... IsNumeric ????

[Connatic]

I feel that i should know how to do this but the ways i have tried don't work;

SELECT linkref
FROM  Derivedaddresses
WHERE right(linkref,1) IS NUMERIC 

SELECT linkref
FROM  Derivedaddresses
WHERE ISNUMERIC(right(linkref,1))

linkref is a varchar field, and has both Alpha ~& numeric characters in it.

I want to select all records where the last character of LinkRef is a number.

I Know that Number based functions don't always work on Varchar fields, but i cannot Convert the Whole field as it also has letters in it.

I tried using the convert function as shown below

SELECT linkref
FROM  Derivedaddresses
WHERE ISNUMERIC(CONVERT(INT,right(linkref,1)))

but this doen't work either. Anybody got a better way (preferably that works!)

-Richard

 

[GHolden]

Hi, ISNUMERIC returns 1 or 0 so you need...

SELECT LinkRef FROM Derivedaddresses
WHERE ISNUMERIC(RIGHT(LinkRef,1)) = 1

There are two ways to write error-free programs; only the third one works.

 

[collierd]

Looks like the use of isnumeric

Try:

SELECT linkref
FROM Derivedaddresses
WHERE ISNUMERIC(right(linkref,1)) = 1


Damian.

 

[Connatic]

Cheers guys, that worked spot on !

 

[SQLBill]

Connatic,

Remember, when you use a WHERE you must compare one thing with another and that's what you failed to do.

-SQLBill

 

[Connatic]

Hi SQLBill,

I knew that the where clause was wrong, i had been hoping that i could use Is Numeric the same as is null

Which works fine in the WHERE clause like:

SELECT LinkRef FROM Derivedaddresses
WHERE LinkRef is null

Obviously i can't, but i thought my examples would show what i was trying to do !!

 

[SQLBill]

It's all in the syntax. The difference is that there is a:

IS NULL

But no:

IS NUMERIC.

The proper syntax is: ISNUMERIC

There's also an ISNULL which is NOT the same as IS NULL.

So with IS NULL you are making a comparison, with ISNUMERIC and ISNULL you aren't (as far as a WHERE clause is concerned).

-SQLBill

 

[GHolden]

SQLBill:

Good Point, well made.

It's pretty easy to see why Connatic made the mistake he made. I had similar problems when learning T-SQL (and I think the same would be true for most people who come from a programming background).

Functions such as ISNUMERIC kind of imply a boolean result, expecially if you come from say a VB background where the following is a valid statement...

If IsNumeric(x) Then
  MsgBox "Numeric"
Else
  MsgBox "Non-numeric"
End If

Where we have the same function with a different result.

Personally I find it very odd that SQL Server doesn't have a boolean data type, do you know if there is any reason for this?



There are two ways to write error-free programs; only the third one works.