troubleshooting Question

String to Integer in SQL Server / Oracle / DB2 SQL

Avatar of DapperDan
DapperDan asked on
9 Comments1 Solution2836 ViewsLast Modified:
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.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 9 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros