CODEWITHSUNDEEP
mysql
user1031742
user1031742

Reputation: 424

Find rows numbers, add 1, then replace string

I have rows similar to this

a:1:{s:5:"value";s:69:"https://www.mypage.com/files/products/product.jpg";}

I would like to extract the number 69, add 1 and replace whole string with

a:1:{s:5:"value";s:70:"https://www.mypage.com/files/products/product.jpg";}

How to approach this? Is this possible to do in mysql?

I use mysql 5.5.

Thank you.

Upvotes: 1

Views: 45

Answers (1)

DxTx
DxTx

Reputation: 3357

Try something like this...

SELECT REPLACE(col, Substring_index(Substring_index(col, '"value";s:', -1), ':"http', 1),
              Cast(Substring_index(Substring_index(col, '"value";s:', -1), ':"http', 1) AS UNSIGNED) + 1) AS col
FROM   TableName

To update the data, do this.

UPDATE TableName
SET    col = REPLACE(col, Substring_index(Substring_index(col, '"value";s:', -1), ':"http', 1),
                          Cast(Substring_index(Substring_index(col, '"value";s:', -1), ':"http', 1) AS UNSIGNED) + 1);

Query step by step

Step 01

SELECT *
FROM   TableName

+-----------------------------------------------------------------------------+
|                                    col                                      |
+-----------------------------------------------------------------------------+
| a:1:{s:5:"value";s:69:"https://www.mypage.com/files/products/product.jpg";} |
+-----------------------------------------------------------------------------+

Step 02 - Get the value

SELECT Substring_index(Substring_index(col, '"value";s:', -1), ':"http', 1)
FROM   TableName 

+-----------------------------------------------------------------------+
| Substring_index(Substring_index(col, '"value";s:', -1), ':"http', 1)  |
+-----------------------------------------------------------------------+
|                                                                    69 |
+-----------------------------------------------------------------------+

Step 03 - Add +1

SELECT Cast(Substring_index(Substring_index(col, '"value";s:', -1), ':"http', 1) AS UNSIGNED) + 1
FROM   TableName 

+---------------------------------------------------------------------------------------------+
| Cast(Substring_index(Substring_index(col, '"value";s:', -1), ':"http', 1) AS UNSIGNED) + 1  | 
+---------------------------------------------------------------------------------------------+
|                                                                                          70 |
+---------------------------------------------------------------------------------------------+

Step 04 - Replace the value with new value

SELECT REPLACE(col, Substring_index(Substring_index(col, '"value";s:', -1), ':"http', 1),
              Cast(Substring_index(Substring_index(col, '"value";s:', -1), ':"http', 1) AS UNSIGNED) + 1) AS col
FROM   TableName 

+-----------------------------------------------------------------------------+
|                                    col                                      |
+-----------------------------------------------------------------------------+
| a:1:{s:5:"value";s:70:"https://www.mypage.com/files/products/product.jpg";} |
+-----------------------------------------------------------------------------+

Online Demo: https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=9df7f1b01e7a8fa33ccc1c222dd3b411

Upvotes: 1

Related Questions