Text matching between columns

[sw9]

I have table1.columna with numerical entries like '101'. Then I have table2.columnb that has entries like 'path/101'.

 

I want to delete everything from table2.columnb where the number after 'path/' matches the number in table1.columna.

 

I'm not sure how to do that since there is nothing else joining these two columns together. Scratching my head. Any ideas?

[XeNoMoRpH1030]

Well, if the portion in front is literally "path/" then it wouldn't be a problem and you could just use CONCAT. My guess is it would differentiate between rows, so RegExp might be the way to go.

[sw9]

Ugh, REGEXP is something I'm still trying to wrap my head around. Do you have an example query I might start with?

 

Thanks much in advance.

[SchweppesAle]

not sure, I guess you can try the LIKE clause

 

$id = 101;
$query = "SELECT * FROM TABLE WHERE column LIKE '%$id'";

[XeNoMoRpH1030]

Actually, that might not be so bad. I'd recommend adding a forward slash like below.

$id = 101;
$query = "SELECT * FROM TABLE WHERE column LIKE '%/$id'";

 

That way if you were searching for id 1, you wouldn't get 11, 101, etc.

 

[sw9]

thanks XeNoMoRpH1030. I was trying to do it direct in mySQL but your way makes way more since. I was able to make a quick while loop and delete all 50,000 applicable entries via your method.