Using MS SQL Server and regex matching, how do you match optional (zero or one instances) of a character or string?

Beamson
Beamson used Ask the Experts™
on
Using MS SQL Server and regex matching, how do you handle optional (zero or one instances) of a character?

models
-----------
H55N6800UK
H55NU8700UK
H60NEC5600UK

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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Developer
Commented:
There is no regex matching in SQL Server. Using LIKE like you do is already the best approach in such a case.

The only other way: Use SQL CLR which implements Regex.

Author

Commented:
Maybe my use of "regex" makes this question unclear.  I don't know what general umbrella term is used for the LIKE conditions in SQL - I used 'regex' but I guess 'SQL's pattern matching' is probably more accurate.

The thrust of what I'm trying to do is the same: reduce the complexity of a LIKE condition with multiple OR clauses by getting the right syntax to do some form of optional character matching where zero or one instance of the character(s) to be matched are matched.

Ultimately, I want to join two tables, the second of which contains a pattern to be matched (incorporating optional characters somehow) and the first table contains a column of stuff which should match those patterns.  I'm not familiar with CLR.  Would I have to create a function, then somehow use this function in the JOIN condition between the two tables?
ste5anSenior Developer

Commented:
As I already wrote:

Using LIKE like you do is already the best approach in such a case.
The only other way: Use SQL CLR which implements Regex.

Read the link.Yes, you need VisualStudio to create a DLL which can be then used in SQL Server.
There are read to use libraries in the wild, but they are not trustworthy.

Author

Commented:
Thanks for your help.  This appears to work fine.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial