MySQL: Extract regexp value from query

Question

I would need to get value from given regexp.

For example:

> :"postalCode";s:4:"3150";

Is there any way I can extract 3150, from this part of column value. Column value stored serialized objects, so postalCode variable can be null type, that way I should check if positive integer follows ;s:POSITIVE_INT:"postalCodeValue

What sort of data is this? I was almost tempted to say JSON, but not quite. — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 26, 2017 at 14:11
it's PHP serialized data @TimBiegeleisen php.net/manual/en/function.serialize.php — Raymond Nijland
– Raymond Nijland, Commented Dec 26, 2017 at 15:07

Tim Biegeleisen · Accepted Answer · 2017-12-26 14:17:00Z

2

Use SUBSTRING_INDEX:

SELECT
    SUBSTRING(SUBSTRING_INDEX(col, '"', -2), 1,
              INSTR(SUBSTRING_INDEX(col, '"', -2), '"') - 1) AS num
FROM yourTable;

This query will extract the last quoted number in your string.

Demo

answered Dec 26, 2017 at 14:17

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

3 Comments

Lululu Over a year ago

I am sorry, I forgot to mention that this is only part of huge value that is stored in column type so it is in the middle of value this part. It has some random value before and after it (:"postalCode";s:4:"3150"; is not full string on which I call these procedures), and I should point on it and fetch value.

Tim Biegeleisen Over a year ago

Does s:4: always separate the postal code from the number?

Lululu Over a year ago

Not, 4 is length of postalCode which is after that inside quotes. It can have different values. But ";s:11:"countryName always comes after postalCode value which I need.

ScaisEdge · Accepted Answer · 2017-12-26 14:07:42Z

0

avoiding regexp you could use some string function eg:

SELECT LENGTH(':"postalCode";s:4:"3150"') - LOCATE(':', REVERSE(':"postalCode";s:4:"3150"'))+1
from dual ;

or

SELECT LENGTH(col_name) - LOCATE(':', REVERSE(col_name))+1
from my_table ;

answered Dec 26, 2017 at 14:07

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Comments

Bernd Buffen · Accepted Answer · 2017-12-26 14:42:28Z

0

It also work with 2 times SUBSTRING_INDEX

SELECT 
SUBSTRING_INDEX (SUBSTRING_INDEX( ':"postalCode";s:4:"3150";', '"',-2) , '"', 1);

answered Dec 26, 2017 at 14:42

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

Collectives™ on Stack Overflow

MySQL: Extract regexp value from query

3 Answers 3

Demo

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related