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?

score 83 · Accepted Answer · answered Feb 10 '15 at 12:51

83

Just use substring_index() twice:

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

answered Feb 10 '15 at 12:51

Gordon Linoff

1,198,228
53
572
709

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 May 25 '18 at 12:00
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. – Fr0zenFyr May 25 '18 at 12:02

score 1 · Answer 2 · edited Dec 23 '18 at 02:58

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 '18 at 02:58

akash

22,224
10
57
85

answered Jun 06 '18 at 12:48

Gajanand Pandey

11
1

MySQL substring between two strings

2 Answers2

Linked

Related