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.
Senior Solution Architect

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 9 Comments.
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>


Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 9 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004