MYSQL Match part of string

Question

I have 2 tables that I need to match. Please see example.

TB1

ID | String1 
1  | Apt 2
2  | Apt 23
3  | Apt 22

TB2

ID2| String2
1  | 23 Apartment
2  | 22 The Block
3  | 2 Complex

I need to find which ID's Match from TB1 to TB2 WHERE the number part of string 1 to string2 match

OUTPUT

 ID | Matching ID2 | String1 | String 2
 1  | 3            | Apt 2   | 2 Complex
 2  | 1            | Apt 23  | 23 Apartment
 3  | 2            | Apt 22  | 22 Apartment

Not clear - Can you explain what you are trying to match based on what values? — Sachin Shanbhag, Oct 28 '11 at 11:53
sorry, yes that is correct I want to mach the number part of string 1 to string2 — Matt Dykstra, Oct 28 '11 at 12:03

score 0 · Answer 1 · answered Oct 28 '11 at 12:02

0

IMHO this is bad table design. If you have to do such matches, store the number in a separate field.

answered Oct 28 '11 at 12:02

m0skit0

25,268
11
79
127

I would but this is how the tables have come to me and I need to run some matches – Matt Dykstra Oct 28 '11 at 12:04
No possibility of doing an ALTER TABLE? – m0skit0 Oct 28 '11 at 12:10
I can get it into csv and can do alter table, however the data is so different I dont know how to make that change. – Matt Dykstra Oct 28 '11 at 12:15
for example: String1 includes Apt 1, Apartment 1, 1 / The Block. The common thing is it contains a number – Matt Dykstra Oct 28 '11 at 12:16
I don't understand your last comments. Also this is screaming for a Foreign Key relationship between TB1 and TB2... – m0skit0 Oct 28 '11 at 13:00

score 0 · Answer 2 · edited May 23 '17 at 11:47

If you really really can't change the table format then I would create a function to strip all of the non-numeric characters from a given string and then use it in your query.

Luckily, this is a task that has been tackled before (hope your Italian is up to scratch)

You could then use the function in your query.

Hence:

select a.ID, b.ID as "Matching ID2",a.String1,b.String2    
from TB1 as a INNER JOIN TB2 as b    
ON LeaveNumber(a.String1) = LeaveNumber(b.String2)
ORDER BY a.ID asc;

Not fool proof and probably not the fastest but it should get you what you want.

Though I would seriously consider altering this table design if you have the chance. Though it sounds as though it is either CSV engine table or populated directly from a CSV in which case you probably don't have too much control over it!

MYSQL Match part of string

2 Answers2