Numeric calculation from VARCHAR fields in MySQL

Question

I have a (simplified) table of results:

id | Result1 | Result2 | Progress
---------------------------------
1  | 1b      | 3c      | NULL
2  | 2c      | 2a      | NULL
3  | 3c      | 2b      | NULL
4  | P3      | 2c      | NULL
5  | 2c      | A       | NULL

I would like to populate the Progress column based on the difference between the numeric part (always first character) of the Result1 and Result2 columns. If the first character of either result column isn't numeric the Progress column should be NULL.

So the results for the table above should be

3c - 1b = 2
2a - 2c = 0
2b - 3c = -1
2c - P3 = NULL
A - 2c = NULL

Thanks for your help.

Possible duplicate of [MySQL/SQL retrieve first 40 characters of a text field?](http://stackoverflow.com/questions/2080860/mysql-sql-retrieve-first-40-characters-of-a-text-field), [Matching first char in string to digit or non-standard character](http://stackoverflow.com/questions/4761507/matching-first-char-in-string-to-digit-or-non-standard-character) or [MySQL Select Query - Get only first 10 characters of a value](http://stackoverflow.com/questions/14959166/mysql-select-query-get-only-first-10-characters-of-a-value). — GregD, Dec 10 '13 at 10:16
[**See that working fiddle**](http://sqlfiddle.com/#!2/34e92/2) — M Khalid Junaid, Dec 11 '13 at 19:36

M Khalid Junaid · Answer 1 · 2013-12-10T14:03:57.227

0

Try this with CASE AND LEFT()

SELECT *,
(CASE WHEN
LEFT(Result1 ,1) - LEFT(Result2 ,1) >=0 OR 
LEFT(Result1 ,1) - LEFT(Result2 ,1) <=0
THEN LEFT(Result1 ,1) - LEFT(Result2 ,1)
ELSE NULL END ) `result`
FROM table

EDIT

First check if first value is numeric or not then calculate

SELECT *,

(CASE WHEN

CONCAT('',LEFT(Result1 ,1) * 1)  =0 THEN NULL

WHEN CONCAT('',LEFT(Result2 ,1) * 1)  =0 THEN NULL

WHEN LEFT(Result2 ,1) - LEFT(Result1 ,1) >=0 OR 

LEFT(Result2 ,1) - LEFT(Result1 ,1) <=0

THEN LEFT(Result2 ,1) - LEFT(Result1 ,1)

ELSE NULL END ) `result`

FROM `progress`

LEFT(str,len)

See working fiddle

edited Dec 10 '13 at 14:03

answered Dec 10 '13 at 10:15

M Khalid Junaid

63,861
10
90
118

Thanks, doesn't quite do it as the difference between the two results columns may be 0 (see second example in my question). – Essex Steph Dec 10 '13 at 11:27

Ezekiel Victor · Answer 2 · 2013-12-11T05:52:48.210

0

UPDATE table
   SET Progress = IF(Result1 RLIKE '^[0-9]' AND Result2 RLIKE '^[0-9]', Cast(Result1 AS SIGNED) - Cast(Result2 AS SIGNED), NULL);

edited Dec 11 '13 at 05:52

answered Dec 10 '13 at 10:15

Ezekiel Victor

3,877
1
27
28

Um, this answer fails to address the problem. – Strawberry Dec 10 '13 at 10:40
Sorry, this doesn't work for rows like the fourth one in my table above - i.e. the first letter isn't an integer. – Essex Steph Dec 10 '13 at 10:46
No problem, I edited it to account for non-numeric inputs; in this case, it will produce NULL for the third column. – Ezekiel Victor Dec 11 '13 at 05:53

score 0 · Accepted Answer · answered Dec 10 '13 at 11:30

Thanks to those who replied, the quick response here pointed me in the right direction - here's what I came up with:

UPDATE mytable SET Progress =
    CASE
        WHEN CAST(Result1 AS SIGNED) > 0 AND CAST(Result2 AS SIGNED) > 0
            THEN CAST(Result2 AS SIGNED) - CAST(Result1 AS SIGNED)
        ELSE NULL
    END

score 0 · Answer 4 · edited Dec 10 '13 at 12:32

0

update table_name
set progress=if(left(Result2,1)regexp'[:alpha:]'=1,null,left(Result2,1))-if(left(Result1,1)regexp'[:alpha:]'=1,null,left(Result1,1)) ;

edited Dec 10 '13 at 12:32

Ondrej Janacek

12,486
14
59
93

answered Dec 10 '13 at 12:13

karnish

79
1
10

Numeric calculation from VARCHAR fields in MySQL

4 Answers4

See working fiddle