Mysql – How to add 100 to all strings starting with a digit in an attribute

MySQL

I have a table doc_properties
with columns

  • id (int)
  • doc_property_category (int)
  • prop_id (int)
  • value (string)

I want to increment all "number-value"-attributes (besides values starting with a number there are also values like "luxury-binding")in column value with 100.

For example in table doc_properties, the column value "0100s" should become "0200s", "1500s" should become "1600s", "1900s" should become "2000s" etc. The alphanumeric digit ends always with 's' and it starts always with 4 digits eg. 0100s, 0200s, 0300s, 1700s, 1800s, 2000s, 2100s.

What I'm looking for is a SQL that:

  1. first check if the value starts with a digit, if so
  2. remove trailing "s" from the string like in "1900s" => "1900"
  3. convert into integer and add 100
  4. convert back into string and add "s" so the result is "2000s".

Is this possible with MySQL only?

My attempt:

CREATE PROCEDURE ROWPERROW()
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
SELECT COUNT(*) FROM doc_properties INTO n;
SET i=0;
WHILE i<n DO 
  UPDATE doc_properties SET value=value + 100 WHERE concat('',value * 1) = value;
  SET i = i + 1;
END WHILE;
End;
;;

I'm missing step 1 and the conversion to integer and back to string.

Can I do the conversion with:

SELECT field,CONVERT(SUBSTRING_INDEX(value,'s',-1),UNSIGNED INTEGER) AS num
FROM doc_properties;

If so how to integrate it into my loop?

Best Answer

It assumes that value field length is always 5 and s is located always at end of the string.

update doc_properties
set value = concat(lpad(cast(left(value, 4) as signed) + 100, 4, '0'), 's')
where substr(value, 1, 1) between '0' and '9';

✓

select * from doc_properties;

id | doc_property_category | prop_id | value
-: | --------------------: | ------: | :----
 1 |                     1 |       1 | 0300s
 2 |                     1 |       1 | 1300s
 3 |                     1 |       1 | 0600s
 4 |                     1 |       1 | 2000s

db<>fiddle here

Related Question