Problems with subquery match searching

[PilotMike]

Hey all..

I need to join on a computer hostname in a couple tables.

In table one, I have just the hostname: 'spam.aviate.org'. In the other table where I need to match on, the hostname field can either have a single hostname that would match exactly ('spam.aviate.org'), a hostname that is close but that I DON'T want to match: ('drspam.aviate.org'), or a list of hostname aliases that contain a delim: 'spam.aviate.org^alias1.aviate.org^alias2.aviate.org'.

I am having a tough time getting exact matches. I have 111 rows in the first table that I need to match up with the second table, which has over a thousand. So far I've tried simple = and LIKE subqueries, but that leaves out all the "multiple hostname" entries that contain the delim.

I next tried LOCATE, but then I was picking up close matches along with exact ones, which won't work ('spam' and 'drspam' would result in a match). Lastly, I began trying using REPLACE to get rid of the delim thinking I could then match on whole words. So far that isn't going too well. I need to maintain the delimiter-separated format of the second table, so that can't be changed.

Any input on how best to handle this search?

Thanks!

 

[DaZZleD]

have you tried to use regular expressions?

 

[PilotMike]

So far I've tried this, which seems like it should work okay:

select outage.id, outage.device, devices.id, devices.hostnames, devices.vender from outage, devices where outage.device = ANY (select replace(hostnames,'^',' ') from devices);

But I get a syntax error.

 

[TonyGroves]

How about:
[tt]
WHERE
CONCAT('^',hostnames,'^')
LIKE('%^spam.aviate.org^%')
[/tt]

-----
ALTER world DROP injustice, ADD peace;

 

[TonyGroves]

To make that clearer, your SQL would look like:
[tt]
SELECT
outage.id,outage.device,devices.id,
devices.hostnames,devices.vender
FROM
outage
JOIN devices
ON
CONCAT('^',devices.hostnames,'^')
LIKE CONCAT('%^',outage.device,'^%')
[/tt]

-----
ALTER world DROP injustice, ADD peace;

 

[PilotMike]

Thanks a lot, guys. This has gotten me a lot closer to the solution! I appreciate it. Now I have to figure out how to avoid getting duplicate matches (UNIQUE maybe?)