RegEx SQL, combine known pattern with variable numbers

Question

I want to check whether there is a known pattern with variable numbers.

This column 'shortcut' has values like this

|shortcuts|
-----------
|ab1
|ab2
|ab23
|abc123

The only thing I've got for my SQL-statement is the alphabetical pattern e.g. 'ab'

So I started with

SELECT * FROM mytable WHERE shortcut LIKE 'ab%'

I only need ab1, ab2 and ab23 and NOT abc12.

Is there a way to modify my statement? There is at least one number, numbers always follow the known pattern and the pattern is the only known value.

Sorry, does it mean you want to also return records like ab12 some text here? — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Sep 9, 2020 at 9:25

Gordon Linoff · Accepted Answer · 2020-09-08 14:21:08Z

1

You can use regular expressions:

where shortcut regexp '^ab[0-9]+'

This says that shortcut starts with "ab" and is followed by at least one digit.

answered Sep 8, 2020 at 14:21

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Thank you very much, this statement did exactly what I needed.

Wiktor Stribiżew · Accepted Answer · 2020-09-09 09:24:48Z

1

You can use

SELECT * FROM mytable WHERE shortcut REGEXP '^ab[0-9]+$'

The ^ab[0-9]+$ regex (see its online demo) matches:

^ - start of string
ab - an ab string (case insensitively, use BINARY after REGEXP to make it case sensitive)
[0-9]+ - one or more digits
$ - end of string.

answered Sep 9, 2020 at 7:38

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges