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:
- first check if the value starts with a digit, if so
- remove trailing "s" from the string like in "1900s" => "1900"
- convert into integer and add 100
- 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?