Avatar of DapperDan

asked on 

String to Integer in SQL Server / Oracle / DB2 SQL

Hello there

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.

Avatar of undefined
Last Comment

8/22/2022 - Mon