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

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?

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

``````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.