MySQL: SELECT DISTINCT REGEX value

Question

In my MySQL database I have a row column called test_column with the following rows:

dtq test dis
ged something fbd
edf something tds
zhs nothing edk
dda anything zhg
hvf nothing ert
asf nothing vbg

I'm looking for the string between the first three and the last three characters. I can get these values with REGEX like this:

^\w{3}\s(\w+)\s\w{3}$

I want to SELECT DISTINCT these values.

Expected output is the following:

test
something
nothing
anything

How can I do that with a MySQL command?

Answer 1

If you are running MySQL 8.0, you can use regexp_replace() as follows:

select distinct regexp_replace(col, '(^\\w{3}\\s)|(\\s\\w{3}$)', '') new_col from mytable

This works by replacing the first and last words (and the following/preceding spaces) with the empty string. The first and last words must be 3 characters long.

Demo on DB Fiddle:

| new_col   |
| :-------- |
| test      |
| something |
| nothing   |
| anything  |

You can make the regex a little more generic so it accepts also starting and ending words that have a length other than 3 characters and sequences of more than one space:

regexp_replace(col, '(^\\w+\\s+)|(\\s+\\w{+$)', '')

Answer 2

Don't need a regexp if this suffices

SUBSTRING_INDEX(col, ' ', 2)

However, this assumes your "3 characters" or \w{3} (which is really 3 alphanumeric characters) is not really the test, but instead the space is critical.

Answer 3

You don't need regex if that is all you want (remove first 4 and last 4 characters):

SELECT DISTINCT SUBSTRING(test_column,5,LENGTH(test_column)-8)
FROM mytable

Demo on dbfiddle.uk

or everything after the first space, up until the second space:

SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(test_column,' ',2),' ',-1)
FROM mytable

Demo on dbfiddle.uk