Dynamic Like

I have 4 street addresses in a table called addr:
123 MAIN ST
123 N MAIN ST
123 S MAIN ST
123 E MAIN ST
The addr table has two cols: addr & dir
the addr col contains the address and the dir col is empty (its the col I want to update)

I have 4 street directions in a table called dir:
N
S
E
W
The dir table has a single col called dir (short for direction).

I could write 4 separate queries based on:
UPDATE addr SET dir=‘N’ WHERE addr LIKE ‘% N %’

Is there a way to do it in one query? If so how?

EDIT:
tried:

UPDATE addr, dir SET addr.dir = dir.dir WHERE addr.addr LIKE ‘% dir.dir %’

But, no joy (though it didn’t cause any errors).

i am not familiar with the “no joy” error message :smile:

try…

addr.addr LIKE CONCAT('% ',dir.dir,' %')

That’s a huge WOW! Thank-you so much r937. I saw CONCAT used that way in a stack overflow, but didn’t think it applied.

Is there a ref for using it that way or some other way to remember using CONCAT that way?

best reference is da manual

https://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat

remember, arguments are strings, but they do not have to be constants/literals

How do you know when to use concat() for the argument? I can’t find guidance on this issue, in the manual.

i’m not sure that level of tautology is in there :wink:

use CONCAT() when you want to concatenate strings

use DATE_FORMAT() when you want to format a date

use SUBSTRING() when you want to take a substring

see the pattern?

Yes. Thanks for helping me r937. You’re a great asset to the sql community.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.