I need to join two tables based on an event id. I've heavily simplified the data for security (and because it shouldn't be relevant to the request). The data looks like this:
| Id |
|---|
| 1234 |
| 56789 |
| UId |
|---|
| 110001234 |
| 110056789 |
The Id and UId link to the same instance (but in different databases), with the UId utilising the leading 1s and 0s. The UId is always 9 characters in length, the Id is preceded by as many leading zeros are necessary to keep the standard length. Id will never start with a zero, and the longest Id would be seven characters.
I'm currently using
SELECT t1.[Id] FROM Table1 t1
JOIN Table2 t2 ON t2.EventUID LIKE CONCAT('%', t1.Id)
But the issue is this is causing (for example) UId 110011234 to join onto Id 1234.
Is there a way for the join to ignore the leading digits and just join based on the exact digits following the leading 1s and 0s?
You haven't told us your DBMS. The following queries are in SQL Server's TSQL dialect. Other DBMS will have similar functions.
You can come from one side or another:
If your DBMS supports LTRIM, then first remove the two ones and then all the zeros in order to get the real number:
SELECT t1.[Id]
FROM table1 t1
JOIN table2 t2 ON LTRIM(LTRIM(t2.eventuid, '1'), '0') = t1.id
(You can of course skip the ones with a SUBSTRING function instead.)
Or, if your DBMS supports a FORMAT function, you'd create the long ID by formatting the shorter one with leading zeros and add the leading ones then.
SELECT t1.[Id]
FROM table1 t1
JOIN table2 t2 ON t2.eventuid = '11' + FORMAT(t1.id, '0000000')
As applying a function on a column will prevent the DBMS from using an index on that column, you'll want to apply the function on the column of the smaller table and leave the column of the larger table unaltered.