I have an issue creating a join between 2 particular tables in a 3rd party database. This database can run on SQL Server, Oracle and DB2.
Table A contains the numeric field aNum.
Table B contains the string field bText.
For the records I wish to join bText contains the value from aNum, converted to a string. However, bText can also be empty or have any other alphanumeric characters.
I want to be able to create a join that says:
aNum = TO_INT(bText)
I have tried various CAST and CONVERT functions on different platforms. All the ones I have tried throw an error when they find a non-numeric character in bText.
I know there are other ways of approcahing this. My current solution is to create a condition like this:
TO_STRING(aNum) = bText
While this works, the performance suffers badly. aNum is indexed and bText is not. The client is reluctant to add an index onto bText.
I am offering 500 points for this.
My ideal solution would be a SQL function that converts text to a numeric, but just returns null if it cannot be converted, throwing no errors.
It can be a single function, or combination of functions, but they must all be standard built-in functions. I cannot add stored procedures onto client machines.
I need a solution that will work on SQL Server, Oracle and DB2, or 3 seperate solutions. It is fine if they all use different functions, as long as they are all standard to that platform.
Alternatively, I will accept any other solution that allows the join to make use of the index on the aNum field.