Kusto Query: Join tables with different datatypes

Question

How would you join two tables based on two columns with same names, but different datatypes? In this example, phone_number is string in table_1 and int64 in table_2. When I try to change datatype from string to int, it changes the values!

table_1
|project name, phone_number
|join kind=fullouter table_2 on $left.name==$right.name and $left.phone_number==$right.phone_number

Thanks

Answer 1

You have issues with your data to begin with.

A phone number is of type string, not an integer.
A phone number might have a leading zero, e.g., 050123456 or non-digit characters e.g., +972123456 or *1234.

If you will try to convert those strings to integer, you will get nulls.
If you convert your integers to string, you will discover that for some of the values you are missing a leading zero.

---

That said, in this specific case, I would recommend converting string to integer, paraphs after removing any non-digit character.

let table_1 = datatable(name:string, phone_number:string)
[
    "John"  ,"050123456"
   ,"Linda" ,"+972123456"
   ,"Ben"   ,"*1234"
   ,"Pam"   ,"012-333-444"  
];
let table_2 = datatable(name:string, phone_number:long)
[
    "John"  ,50123456
   ,"Linda" ,972123456
   ,"Ben"   ,1234
   ,"Pam"   ,12333444
];
table_1
| project name, phone_number = tolong(replace_regex(phone_number, @"\D+", ""))
| join kind=fullouter table_2 on $left.name==$right.name and $left.phone_number==$right.phone_number

name	phone_number	name1	phone_number1
John	50123456	John	50123456
Linda	972123456	Linda	972123456
Ben	1234	Ben	1234
Pam	12333444	Pam	12333444

Fiddle