Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL substring between two strings

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?

like image 542
Haohmaru Avatar asked Feb 10 '15 12:02

Haohmaru


People also ask

How do I slice a string in MySQL?

MySQL SUBSTRING() Function The SUBSTRING() function extracts a substring from a string (starting at any position). Note: The SUBSTR() and MID() functions equals to the SUBSTRING() function.

What is substring index in MySQL?

Definition and Usage. The SUBSTRING_INDEX() function returns a substring of a string before a specified number of delimiter occurs.

How do I get the last 3 characters of a string in SQL?

It could be this using the SUBSTR function in MySQL: SELECT `name` FROM `students` WHERE `marks` > 75 ORDER BY SUBSTR(`name`, -3), ID ASC; SUBSTR(name, -3) will select the last three characters in the name column of the student table.


2 Answers

Just use substring_index() twice:

SELECT substring_index(substring_index(licence_key, 'contract=', -1),                        'issued=', 1) FROM table; 
like image 106
Gordon Linoff Avatar answered Sep 20 '22 09:09

Gordon Linoff


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>%'; 
like image 39
Gajanand Pandey Avatar answered Sep 22 '22 09:09

Gajanand Pandey