there are two tables that i try to merge together using ID
table 1 is like:
| ID | feature |
|---|---|
| 1 | blue |
| 2 | red |
| 3 | black |
table 2 has extra text in front of ID, looks like:
| ID | location |
|---|---|
| num1 | az |
| num2 | ca |
| num3 | ny |
thank you for your help!
CodePudding user response：
You could do:
JOIN table2 ON SUBSTRING(table1.ID, 4, 100) = cast(table2.ID as varchar(8))
CodePudding user response：
SELECT *
FROM table1
JOIN table2
ON 'num' table1.ID = table2.ID
Concatenate the literal 'num' and the ID of table 1 to match with the ID of table 2.
Some SQL implementations use || to concatenate instead of .
CodePudding user response：
Just substring the Table2 id so you get only the numeric part, then you can join that calculated id to the Table1 id