16

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}') > 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 is basically a dup of: stackoverflow.com/questions/4021507/… – Nathan Feger Aug 14 '15 at 17:28
  • Without using a UDF there is no built in functionality to retrieve the matched pattern from the REGEXP function and the other matching methods rely on knowing the full string you are matching for which doesnt work in this situation – Payload Sep 16 '16 at 0:45
4

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)
)
| improve this answer | |
1

Unfortunately, MySQL's regular expression function return true, false or null depending if the expression exists or not.

The trick in effecting the desired behavior is to determine which substring begins with the character you care about, has the correct length, and is followed by a number. A series of substring_index functions are used to extract the string...

set @string:='Lorem D9801 ipsum dolor sit amet';
select
case when @string like '% D____ %' and cast((@num:= substring_index(substring_index(@string,concat(substring_index(@string,' D',1),' D'),-1),' ',1)) as signed) between '0' and '9999' then concat('D',@num)
     when @string like '% D% D____ %' and cast((@num:= substring_index(substring_index(@string,concat(substring_index(@string,' D',2),' D'),-1),' ',1)) as signed) between '0' and '9999' then concat('D',@num)
     when @string like '% D% D% D____ %' and cast((@num:= substring_index(substring_index(@string,concat(substring_index(@string,' D',3),' D'),-1),' ',1)) as signed) between '0' and '9999' then concat('D',@num)
     when @string like '% D% D% D% D____ %' and cast((@num:= substring_index(substring_index(@string,concat(substring_index(@string,' D',4),' D'),-1),' ',1)) as signed) between '0' and '9999' then concat('D',@num)
     when @string like '% D% D% D% D% D____ %' and cast((@num:= substring_index(substring_index(@string,concat(substring_index(@string,' D',5),' D'),-1),' ',1)) as signed) between '0' and '9999' then concat('D',@num)
end as test_case;
+-----------+
| test_case |
+-----------+
| D9801     |
+-----------+
1 row in set (0.00 sec)
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.