Sql join on string = integer to work on any DBMS

Question

I need jointwo tables in a column that are defined as a row in one table and as a whole in the other. In both columns, the actual data is stored in integers. It is given to me by design - I cannot change it.

Therefore, when I join MySQL, this is normal - it does the conversion silently. PostgreSQLcomplains. There are operators CASTthat I can add to the query so that the strings are converted to integers, but the CAST function is defined differently in different DBMSs.

Is it possible to write this query the way it works in all (or many) RDBMSs? Also, is there an abstraction DB layer that can do this for me? ADODBalready used in the project, but I don’t see if and how it can be useful in solving this problem.

Thank.

Answer 1

Since you cannot CASTto the INTsame for each database due to data types, you can specify your number field in CHAR:

CAST(a.numeric_Field AS CHAR(5))` = b.stringfield

This will work on Postgresql, MySQL, SQL Server, not sure about others.