Problem with LIKE

[ShadowMage]

I have a field that contains a tilde delimited string of rep codes. For example:Rep1~Rep2~Rep3~~~~ I am currently using LIKE to select a particular rep code:

OrderHed.SalesRepList LIKE '%Rep1%'

Problem is, with the above clause, codes like Rep12 are also selected. Is it possible to write a clause that will select only those with Rep1?I know that I can do something like this:

OrderHed.SalesRepList LIKE 'Rep1~%' OROrderHed.SalesRepList LIKE '%~Rep1~%' OROrderHed.SalesRepList LIKE '%~Rep1'

but that seems really long winded. There has to be something simpler. Or is this the only way?

[justsomeguy]

That's the way I've usually done it when I have to find one identifier in a list like that in a single field. That's an argument for using a separate lookup table instead of a single field. You could also use a regular expression search but I'm not sure how that would affect performance.

[ShadowMage]

That's the way I've usually done it when I have to find one identifier in a list like that in a single field. That's an argument for using a separate lookup table instead of a single field. You could also use a regular expression search but I'm not sure how that would affect performance.

Ok, thanks. Guess I'll do it that way then. I read about the regex search but it says it isn't binary safe, and I have no idea what charset this database uses (or the application that writes to it, I guess) so I opted out of the regex option. But now that you mention performance, it gives me a little more reason to stay away.