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?
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.
Definition and Usage. The SUBSTRING_INDEX() function returns a substring of a string before a specified number of delimiter occurs.
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.
Just use substring_index()
twice:
SELECT substring_index(substring_index(licence_key, 'contract=', -1), 'issued=', 1) FROM table;
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>%';
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With