2

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?

3

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.