Using MS SQL Server and regex matching, how do you handle optional (zero or one instances) of a character?
SELECT model FROM models WHERE (model LIKE 'H[0-9][0-9]N[0-9][0-9][0-9][0-9]UK') OR (model LIKE 'H[0-9][0-9]NU[0-9][0-9][0-9][0-9]UK') OR (model LIKE 'H[0-9][0-9]NEC[0-9][0-9][0-9][0-9]UK')
The SELECT statement above returns the 3 rows but uses an OR with three different regex expressions. What I would like to do is use a single regex expression where the U or EC in the 5th character position are optionally matched.
Ideally a single regex expression to handle this would be something like:
'H[0-9][0-9]N Optional U OR Optional EC [0-9][0-9][0-9][0-9]UK'
The matching needs to be relatively tight and just using the % wildcard would accidentally match more items in the database than desired.
In practice, I will have one table of models to match (tens of thousands of records) and a second table of model-related data that includes the regex match in one column. Then I'll simply join the tables in the form: SELECT * FROM MODELS LEFT OUTER JOIN MODELINFO ON MODELS.MODEL LIKE MODELINFO.REGEX. There are already a huge number of permutations of the regex matches needed to cover my model data. Inability to use an optional character in the regex will add a lot of work and double up a lot of rows.