CODEWITHSUNDEEP
sqlregexdatabasepsql
imadirtycunit
imadirtycunit

Reputation: 125

Regex Help for wildcard lookup

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:

Non-Valid Examples that explicitly need to not show up

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?

Upvotes: 1

Views: 37

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626794

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]

Upvotes: 1

Related Questions