How to rewrite my MySQL update statement to eliminate the warnings "Truncated Incorrect INTEGER Values"?

Question

I am using MySQL 5.5.37. I want to exclude warnings from my update instruction, which are shown below ...

update resource r 
    set grade_id = convert(substring_index(substring_index(
                   r.description, 'Grade ', -1), ' ', 1), unsigned integer) 
    where r.description like '% Grade%' 
      and CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(
                   r.description, 'Grade ', -1), ' ' ,1),UNSIGNED) > 0;

Query OK, 0 rows affected, 7 warnings (0.02 sec)
Rows matched: 1333  Changed: 0  Warnings: 7

mysql> show warnings;
+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: ''            |
| Warning | 1292 | Truncated incorrect INTEGER value: ''            |
| Warning | 1292 | Truncated incorrect INTEGER value: ''            |
| Warning | 1292 | Truncated incorrect INTEGER value: 'MyCo'        |
| Warning | 1292 | Truncated incorrect INTEGER value: 'MyCo'        |
| Warning | 1292 | Truncated incorrect INTEGER value: 'MyCo'        |
| Warning | 1292 | Truncated incorrect INTEGER value: 'MyCo'        |
+---------+------+--------------------------------------------------+

I don’t understand how I can rewrite my request differently, considering that none of the updated values ​​matches what the warnings warn about. BElow is my query where I list various values ​​that are updated ...

mysql> select distinct substring_index(substring_index(
              r.description, 'Grade ', -1), ' ', 1)
          from resource r
          where r.description like '% Grade%'
            and CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(
              r.description, 'Grade ',-1),' ',1),UNSIGNED) > 0;
+-----------------------------------------------------------------------+
| substring_index(substring_index(r.description, 'Grade ', -1), ' ', 1) |
+-----------------------------------------------------------------------+
| 7                                                                     |
| 8                                                                     |
| 11                                                                    |
| 9                                                                     |
| 12                                                                    |
| 10                                                                    |
| 6                                                                     |
+-----------------------------------------------------------------------+

How do I rewrite my update statement so that it updates the same values ​​without tryihng to truncate the wrong integers?

Answer 1

, - WHERE

CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(r.description, 'Grade ',-1),' ',1),UNSIGNED) > 0

, - INTEGER '' MyCo.

| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: ''            |
| Warning | 1292 | Truncated incorrect INTEGER value: ''            |
| Warning | 1292 | Truncated incorrect INTEGER value: ''            |
| Warning | 1292 | Truncated incorrect INTEGER value: 'MyCo'        |
| Warning | 1292 | Truncated incorrect INTEGER value: 'MyCo'        |
| Warning | 1292 | Truncated incorrect INTEGER value: 'MyCo'        |
| Warning | 1292 | Truncated incorrect INTEGER value: 'MyCo' 

, description resource.

description
Foo Grade 100 Bar
Foo Grade 99 Grade 
Foo Grade 98 Grade MyCO

, Grade. SUBSTRING_INDEX(SUBSTRING_INDEX(r.description, 'Grade ',-1),' ',1),. , , , . , column.If , UNION. .

.

   update resource r 
    set grade_id = convert(substring_index(substring_index(
                   r.description, 'Grade ', -1), ' ', 1), unsigned integer) 
    where r.description like '% Grade%' 
    and substring_index(substring_index(r.description, 'Grade ', -1), ' ', 1) REGEXP '[0-9]+';

, .

Answer 2

- WHERE, SELECT. ( ) 0

CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(
          r.description, 'Grade ',-1),' ',1),UNSIGNED) > 0

retuns false. , . select, , :

select distinct 
    substring_index(substring_index(
        r.description, 'Grade ', -1), ' ', 1),
    CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(
        r.description, 'Grade ',-1),' ',1),UNSIGNED),
        r.description
from resource r
where r.description like '% Grade%';

: http://rextester.com/TVHN10678

, .

Answer 3

trim() , , ? .

Answer 4

, :

r.description '% Grade%'        SUBSTRING_INDEX (SUBSTRING_INDEX (                    r.description, 'Grade', -1), '', 1) REGEXP '^ [0-9] + $';

, : CONVERT - :

r.description '% Grade%'        SUBSTRING_INDEX (SUBSTRING_INDEX (                    r.description, 'Grade', -1), '', 1) > 0;

(- , , - " " ).

Answer 5

- resource.description '', MyCo . , convert, .

, , , , resource.description .

select distinct substring_index(substring_index(r.description, 'Grade ', -1), ' ', 1)
from resource r 
order by r.description

EXPLAIN . . MySQL, , , - , where.