MySQL substring between two strings

Question

I need a hand to solve a problem with my column field. I need to extract the string in between these two different "patterns" of strings for example:

[...string] contract= 1234567890123350566076070666 issued= [string ...]

I want to extract the string in between 'contract=' and 'issued='

At the present moment I'm using

SELECT substring(substring_index(licence_key,'contract=',-1),1,40) FROM table

The problem is that this string in between doesn't have always 40 characters so it's not fixed length and so the data that comes before and after that. It's a volatile data.

Do you known how I can handle that?

Answer 1

Just use substring_index() twice:

SELECT substring_index(substring_index(licence_key, 'contract=', -1),
                       'issued=', 1)
FROM table;

Answer 2

If this string does not match then give the total result.

If you want to replace then you can use like this.

UPDATE questions set question= REPLACE(question, '<xml></xml>', '') WHERE question like '%<xml>%';

UPDATE questions set question= REPLACE(question, substring_index(substring_index(question, '<xml>', -1), '</xml>', 1), '') WHERE question like '%<xml>%';