Regex Help for wildcard lookup

Question

I am having issues with this wildcard lookup, not sure why this doesn't work:

I am looking up example Sales Agent 42. As you can imagine being sales, they do not really care about garbage in = garbage out. So their agent codes are usually a mess to sort through.

Valid Examples for Agent 42:

42
30-42-22-holiday
42easter
42-coupon
42coupon-423355
29-42sale-52

Non-Valid Examples that explicitly need to not show up

A4290042
4297901
42cmowc209d
o203f9j42po0

Here is the most successful model I came up with:

SELECT company_id, agent
FROM cust_data
WHERE (agent = ('42') OR agent LIKE ('42%-%') OR agent LIKE ('%-%42') OR agent LIKE ('%-%42%-%') OR agent LIKE ('42[a-z]%-%') OR agent LIKE ('%-%42[a-z]%') OR agent LIKE ('%-%42[a-z]%-%') OR agent LIKE ('42[a-z]%'))

I get most of the valid ones to return and none of the non-valid ones, but I still can't seem to grab the examples like 42easter or 29-42sale-52 even though I am telling it to grab that style...

Any suggestions?

well that worked wonderfully. did not know about the "^" symbol, would be more efficient to exclude numerics than look for only alpha's. Many thanks! — imadirtycunit, Mar 29 '16 at 18:40
I posted although I am a bit puzzled by your `42cmowc209d` and `o203f9j42po0` examples — Wiktor Stribiżew, Mar 29 '16 at 18:43
those were examples i would get sometimes that needed to be excluded or not counted in the look up. sometimes the sales throws in random codes that just coincidentally have their agent code thrown in. They are few and easy to spot in the outputs so i think i might just have to live with hand pulling those... — imadirtycunit, Mar 29 '16 at 19:13
unfortunately so, but your solution helped immensely. very simple and elegant. — imadirtycunit, Mar 29 '16 at 21:00

score 1 · Accepted Answer · answered Mar 29 '16 at 18:42

If you need to match 42 that is not surrounded with digits, you can use alternations with anchors (^ standing for the start of string and $ standing for the end of string) and negated character classes:

WHERE agent ~ '(^|[^0-9])42($|[^0-9])'

See the regex demo

Explanation:

(^|[^0-9]) - either the start of the string ^ or a non-digit [^0-9]
42 - literal 42
($|[^0-9]) - end of string $ or a non-digit [^0-9]

Regex Help for wildcard lookup

1 Answers1