sql join on string = integer to work on any RDBMS

Question

I need to join two tables on column, which in one table is defined as string and in other as integer. In both columns actual data stored is integers. This is given to me by design - I can't change this.

So when I do join MySQL is fine - it does conversion silently. PostgreSQL complains. There are CAST operators, which I can add to query to have strings converted to integers, but CAST function is defined in different RDBMSs differently.

Can I write this query in the way that it works in all (or many) RDBMSs? Alternatively, is there DB abstraction layer, which can do this for me? ADODB is already used in the project, but I can't see if and how it can be helpful in solving this problem.

Thank you.

`CAST(expr AS type)` is ANSI SQL and should work in all RDBMS — zerkms, Dec 25 '14 at 06:39
This is how it is defined in MySQL: CAST(VAR AS UNSIGNED) and here it is in PostgreSQL: CAST(VAR AS INTEGER) — aavagyan, Dec 25 '14 at 06:45
I don't think such type exists in MySQL (http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html) — aavagyan, Dec 25 '14 at 06:51
I do think it exists: http://dev.mysql.com/doc/refman/5.6/en/numeric-types.html — zerkms, Dec 25 '14 at 06:52
Well, I've just run it in MySQL 5.5.x to make sure and getting error for 'integer'. 'unsigned' works, but it is no good... — aavagyan, Dec 25 '14 at 06:56
Seems like you're right. Oh god, it seems like mysql was developed with as much incompatibilities as possible :-S — zerkms, Dec 25 '14 at 06:58

score 6 · Accepted Answer · answered Dec 25 '14 at 07:11

6

Since you can't CAST to INT in the same way for each database due to data types, you can cast your numeric field to CHAR:

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

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

answered Dec 25 '14 at 07:11

Hart CO

34,064
6
48
63

I'll give it a try and report back. It is not ideal because: a) joining on 'string' column/values is slower than on integers b) the actual data is integers, so this is rather 'wrong' way of doing things. – aavagyan Dec 25 '14 at 07:29
This indeed works. Performance of query 2x worse and I didn't find yet possibility to re-write it to run faster, but this - at least - works. Thank you! – aavagyan Dec 25 '14 at 21:34

score 0 · Answer 2 · answered Nov 26 '19 at 17:06

0

You can also cast string value to int.

SELECT tb1.* FROM tb1
  INNER JOIN tb2         
  ON tb1.id = tb2.stringfield::int;

answered Nov 26 '19 at 17:06

Vlad Bezden

83,883
25
248
179

Where did the `::int` come from? – Jin Kwon Nov 07 '22 at 09:27

sql join on string = integer to work on any RDBMS

2 Answers2