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

Question

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?

Answer 1

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

Answer 2

UPDATE doc_properties SET value =
CONCAT(
IF(value LIKE "0%", "0", ""),
CONCAT(CONVERT((CONVERT(value, UNSIGNED INTEGER) + 100), CHAR(50)), "s")
)
WHERE value REGEXP "^[0-9]"

Explanation: This update will only touch rows where the first character is a digit as a result of the WHERE clause. The CONVERT automatically truncates the trailing characters, adds 100 and converts back to a string before concatenating the s. It also checks if the first digit was a 0 and if so prefixes to the result.

Change CHAR(50) to the string type which fits your database schema.