How to use SUBSTRING using REGEXP in MySQL I have the following situation. I have to substring regular expression from description using MySQL.
Description:


  Lorem D9801 ipsum dolor sit amet


Where D9801 is REGEXP. Every strong text description has different content but my regexp should looks like: REGEXP 'D[[:digit:]]{4}'

REGEXP always has "D" at the beginning and "xxxx" - 4 digits at the end: Dxxxx

I know the REGEXP returns only true/false value, but how can I make query to return only 'D9801' value?

I tried something like this:

SELECT SUBSTRING (description, LOCATE(REGEXP 'D[[:digit:]]{4}', description), 5)
FROM (
   SELECT "Lorem D9801 ipsum dolor sit amet" AS description
) temp


I know it's wrong, so I try with this:

SELECT 
    id, 
    SUM(description REGEXP 'D[[:digit:]]{4}') AS matches, 
    CASE
        WHEN (SUM(description REGEXP 'D[[:digit:]]{4}') &gt; 0) THEN 
            SUBSTRING(description, LOCATE( /*POSITION_OF_REGEXP_IN_DESC*/ , description), 5)
        ELSE 'Brak schematu'
    END AS show_substr FROM ps_description GROUP BY id;


But how to find position of regexp?

I heard about UDF but I cannot use it, I use OVH hosting.
This would need to use the LOCATE and SUBSTRING syntax to get the information out of the string.
The basic locate syntax you would need is explained here.
LOCATE (search str, str, [position])

search str = A string which will be searched for.
str =A string which is going to be searched.
position (optional)= Position from where (within the second argument) the searching will start .

While the substring function you need is explained here
SUBSTRING(str, pos, len)

str=  A string.
pos=  Starting position.
len=  Length in characters.

The easier way of viewing this is to think of substring as the following
SUBSTRING(str FROM pos FOR len)
The sytax I used to get the second word is below, I took advantage of the spaces that are constantly around the second word you are trying to extract.
declare @String varchar(50) ='Lorem D9801 ipsum dolor sit amet'

SUBSTRING
(
@String,
LOCATE(' ', @String),
LOCATE(' ', @String, (LOCATE(' ', @String) + 1)) - LOCATE(' ', @String)
)