Isnumeric in a Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Isnumeric in a Join

Author

Topic

BendJoe
Posting Yak Master

128 Posts

Posted - 2010-03-17 : 18:45:15

I need to make a join between two tables .One of the joining columns have non numeric as well as numeric data.

I need the join to consider only the numeric and discard the non numeric.

inner join on A.ID =onlynumeric(B.Val)
Is there a short way to achieve this.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-17 : 18:55:04

Not sure if you can do it right there, but you could use a derived table. Here's an example:

SELECT ...
FROM TableA a
JOIN (SELECT Val, ... FROM TableB WHERE IsNumeric(Val) = 1) b
ON a.Val = b.Val

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Kristen
Test

22859 Posts

Posted - 2010-03-17 : 18:56:49

Assuming that A.ID is a Positive INT (and not floating point) I would do


SELECT COl1, Col2
FROM TableA AS A
     JOIN
     (
         SELECT Val, Col3, Col4, ... -- Just the columns you NEED!
         FROM TableB
         WHERE B.Val NOT LIKE '%[^0-9]%'
     ) AS B
         ON CONVERT(int, B.Val) = A.ID

This works around a couple of issues:

IsNumeric() will be TRUE for floating point numbers, and some things like "1e2" which might be in your B.Val column

Also:


SELECT COl1, Col2
FROM TableA AS A
     JOIN FROM TableB AS B
         ON B.Val NOT LIKE '%[^0-9]%'
        AND CONVERT(int, B.Val) = A.ID

may well fail because SQL does not guarantee to perform AND operations in order (there's a technical term for it - my ageing memory has temporarily forgotten! - procedural languages will evaluate "IF AAA AND BBB" by testing AAA first, and if that is False the BBB will not be evaluated at all. That is not necessarily the case in SQL, and thus the "CONVERT(int, B.Val) = A.ID" may attempt to be evaluated where B.Val is not a valid integer.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-17 : 19:11:11

I like Kristen's reply better!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Kristen
Test

22859 Posts

Posted - 2010-03-17 : 19:15:33

Its pretty much the same as yours, I just used more words - as usual!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-18 : 03:25:28

<<
may well fail because SQL does not guarantee to perform AND operations in order
>>

Most likely from version 2005 onwards

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources