Dynamically create a regex pattern from input string

Question

I have multiple strings stored in my database called order number pattern. This pattern is dictated by the master user/company when setting up the application for themselves.

Therefore any user that creates an order from their company has to follow this pattern. The pattern set could be anything like '000000' or 'AAA00000' or 'AA00aaa000' and so on (mixture of numbers, upper case letters, lower case letters and special characters)

What I need to do is to validate that when an user enters the order number, it matches the pattern set by the company i.e. if the user enters BX12-xyz-345 then check that it matches pattern 'AA00-aaa-000'.

What I thought would be ideal is to generate a regex pattern based on the current pattern and store that against the customer record therefore it makes it easier for me to then match using Regex.match function. The only problem with this is that I have to manually create the regex patterns for each of our approx 250+ customers and therefore was wondering if there is way where I can pass in a string and it returns me a regex pattern for that string.

Ideally if I could do this in SQL server (vial bulk update) if not I do not mind creating a one time exe in C# that can go and update each record with its regex pattern and also change the application such that in future it only stores the regex pattern in the database.

I fear the answer is you could probably write one to sit there and pattern match and decide that this one has 3 letters, followed by 6 numbers, and that one is 2 letters 8 numbers 2 letters.. but.. it would be quicker to type in the 250 codes. — BugFinder
– BugFinder, Commented Dec 5, 2016 at 15:25
The problem is that when we then have a new customer and they enter a pattern of say AA-0000-aaa then i have remember to go and update the regex for that record and if I forget then system would then not function properly — CodeNinja
– CodeNinja, Commented Dec 5, 2016 at 15:29
Agreed. There are tools out there to assist with figuring out possible patterns, but it's very difficult to be certain you found the correct pattern. In your example, if a user enters BX12-xyz-345, how do you know the opattern isn't AX##-aaa-###? Or if any of those fields may be alphanumeric? You're much better off requiring a pattern to be provided by the customer during the requirements gathering/onboarding phase. If you're having a problem remembering to do stuff, look into process tooling. If you forget to do something like this, you're probably missing other things too. — Pete M
– Pete M, Commented Dec 5, 2016 at 15:29
Why dynamic? If there's only the things you've outlines here you can use one regex. ^\d{6}$|^[A-Z]{3}-?\d{5}$|^[A-Z]{2}-?\d{2}-?[a-z]{3}-?\d{3}$ This specific regex would check that the code is in the format 000000, AAA-00000, AAA00000, AA00aaa000 or even AA-00-aaa-000 or AA00-aaa-000, etc. — Alfie Goodacre
– Alfie Goodacre, Commented Dec 5, 2016 at 15:31
Since REGEX is extremely limited in SQL Server, this would be the last place I would try to handle it. e.g., don't do the check in SQL. Just my two cents. — S3S
– S3S, Commented Dec 5, 2016 at 15:31

Alex K. · Accepted Answer · 2016-12-05 15:33:00Z

2

Assuming A/a/0 represent A-Z, a-z and 0-9 & you don't use any reserved pattern characters you can build a pattern mask:

declare @pattern varchar(max) = 'AA00-aaa-000'

set @pattern = replace(replace(replace(@pattern COLLATE Latin1_General_BIN, 'A', '[A-Z]'), 'a', '[a-z]'), '0', '[0-9]')

select @pattern

[A-Z][A-Z][0-9][0-9]-[a-z][a-z][a-z]-[0-9][0-9][0-9]

if 'BX12-xyz-345' like @pattern print 'y'

answered Dec 5, 2016 at 15:33

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamically create a regex pattern from input string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related