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?

Gordon Linoff · Accepted Answer · 2015-02-10 12:51:36Z

101

Just use substring_index() twice:

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

answered Feb 10, 2015 at 12:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Fr0zenFyr Over a year ago

That's clever, indeed... I was trying to migrate a unnormalized DB on ver. 5.5 to 5.7 (I'm a little skeptical about ver. 8). It had a column that has json string, but luckily, the structure of json is consistent throughout the table. This saved me a lot of CPU, I didn't want to get all the data into a script and then insert each row into new table.

Fr0zenFyr Over a year ago

BTW, I'm aware of json support in mySQL ver above 5.6. I just don't see the point of storing JSON in a relational DB unless you have no other choice or if you don't have to ever do a lookup in that column to reduce the complexity of DB.

Akash Thakare · Accepted Answer · 2018-12-23 02:58:55Z

1

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>%';

edited Dec 23, 2018 at 2:58

Akash Thakare

23k11 gold badges64 silver badges89 bronze badges

answered Jun 6, 2018 at 12:48

Gajanand Pandey

211 bronze badge

Collectives™ on Stack Overflow

MySQL substring between two strings

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related