Build REGEX in SQL automatically

Question

I'm now facing a trouble in building a SQL regex searching at runtime.

Somewhat its like:

AA?001

Data:
AAA001
AAB001
AA7001
AA70012
AB0001

Result:
AAA001
AAB001
AA7001

At first, I was thinking about the way that splitting it by [+?], then the conditions will be something like:

Startwith('AA') AND Endwith("001") AND LENGTH = 5)

But this way is not gonna work with pattern: A?0?1. So, I would probably have to build a Regex at runtime, but do not have any clue to do that.

Any suggest/idea/recommend would be grateful.

You mean this AA.001 ? Add anchors if necessary, ^AA.001$ — Avinash Raj
– Avinash Raj, Commented Feb 9, 2015 at 4:50
ohh right, just have to replace ? to dot. I dont even know about . before. So new to REGEX. thank you @AvinashRaj — Kingcesc
– Kingcesc, Commented Feb 9, 2015 at 4:58

vks · Accepted Answer · 2015-02-09 05:49:29Z

1

^AA[A-Z0-9a-z]001$

Try this.See demo.

Quantifier: ? Between zero and one time

? does not match a character on its own.It works on character preceded by it.

Also add ^$ anchors to disallow a partial match.

^AA\S001$

If you want to match any character except space.

answered Feb 9, 2015 at 4:58

vks

68.1k11 gold badges96 silver badges132 bronze badges

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

2 Comments

thank you, but the regex depends on what user inputs. Like: ???001 or A???01, ... So the way AvinashRaj mentioned above will work for me :)

@Kingcesc . will allow AA 001 too.Do you want to allow space too.Use \S if you want all but no space.