I have a table
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?
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?