SQL Is there a way to link two tables with the same value but different formats?

Question

So I have two columns from two different databases that I would like to link.

Problem is that my first column outputs the numbers with this format "1 789 987" and my second column outputs the data "0000000001789987"

How can I write my WHERE sql forumla to idententify these as matching?

Ok so I pulled out the qrys to excel to provide you with more information. Here are the different tables.

Looks like Tbl2 has NUM column set to text. And even though the QRY in the program gave spaces to the numbers in Tbl1 it looks like the qry removed them shrug

SELECT *
FROM "Tbl1","Tbl2"
WHERE "Tbl1"."num" = "Tbl2"."num" 
AND "Tbl1"."Date" BETWEEN '2019-01-21' AND '2019-01-25'

I hope the information became abit clearer. I'm new to SQL and Stackoverflow, i'll try and improve my questions information in the future.

You want to JOIN two tables on this column or make a WHERE column1 = column2 ? — Mickaël Leger, Jan 22 '19 at 09:12
What is the type of the columns and how large/long can those numbers be? — Joakim Danielson, Jan 22 '19 at 09:19
Numbers don't have spaces. What are the types? Sample data in the form of a text table is very helpful. — Gordon Linoff, Jan 22 '19 at 12:00
I've updated my question abit. Maybe hope it clears things up. Yes it's a WHERE I wanna use — Kalween, Jan 22 '19 at 19:20

score 2 · Answer 1 · answered Jan 22 '19 at 09:22

2

Well, to transform format 1 to format 2 you can try something like this :

set @format1 = "1 789 987";
set @format2 = "0000000001789987";

select LPAD(REPLACE(@format1, ' ', ''), 16, "0") as format1, @format2 as format2

Output is :

====================================
     format1     |     format2
====================================
0000000001789987 | 0000000001789987

This way format1 looks like format2 if you test it. The REPLACE remove the ' ' and the LPAD will fill the string with 0 untill the string is 16 char length like the format2.

So you can use this in you WHERE condition :

...WHERE LPAD(REPLACE(your_first_column, ' ', ''), 16, "0") = your_other_column

Now you can try to transform both column in int too, you didn't provide lot of information about those format so hard to find the best solution !

answered Jan 22 '19 at 09:22

Mickaël Leger

3,426
2
17
36

Judging by my edited post, how would you go by this? – Kalween Jan 22 '19 at 18:45
Well, if the format 2 is always 16 char long, my solution should works : `WHERE "LPAD(REPLACE(Tbl1.num, ' ', ''), 16, "0") = Tbl2.num` – Mickaël Leger Jan 22 '19 at 22:15
So I get this error. - DB2 for i5/OS]SQL0206 - Column or global variable 0 not found. Could it be that I have to turn them both into INT? – Kalween Jan 23 '19 at 11:26
I removed the commas in "0" and it worked. Thank you so much for the help Mickael! – Kalween Jan 23 '19 at 11:50

DDS · Answer 2 · 2019-01-22T10:33:57.187

1

This cast may fit for you:

NOTE: tbl1 contains ids like: 1 789 987

select *
from tbl1 join tbl2 on (
                        cast( -- casts to integer
                               replace(tbl1.text_id, ' ', '') -- removes whitespaces
                                 as int) = 
                        tbl2.numeric_id -- join on second table
                        )

In any case please provide sample data and a testable example what you did and the results you need

edited Jan 22 '19 at 10:33

answered Jan 22 '19 at 09:15

DDS

2,340
16
34

1

Don't you need to cast both columns? – Joakim Danielson Jan 22 '19 at 09:17
1

On second column 'implicit cast' sohould work. I prefer using the least amount of cast possible as, with time, can lead into issues – DDS Jan 22 '19 at 09:19

SQL Is there a way to link two tables with the same value but different formats?

2 Answers2