mysql字符串函数substr用法二例

mysql> SELECTSUBSTRING('Quadratically',5);
       -> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
       -> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
       -> 'ratica'
mysql> SELECT SUBSTRING('Sakila', -3);
       -> 'ila'
mysql> SELECT SUBSTRING('Sakila', -5, 3);
       -> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR2);

UPDATE pic_recommend r,(
    SELECT id,url,SUBSTR(url FROM 33) idhtml
    FROM medical_pic_recommend
    WHERE type=1
) t
SET r.article_id=SUBSTR(t.idhtml FROM 1 FOR LENGTH(t.idhtml)-5)
WHERE t.idhtml AND r.id=t.id;

UPDATE pic_recommend r,(
    SELECT id,url,SUBSTR(url FROM 25) idhtml
    FROM medical_pic_recommend
    WHERE type=2
) t
SET r.article_id=SUBSTR(t.idhtml FROM 1 FOR LENGTH(t.idhtml)-5)
WHERE t.idhtml AND r.id=t.id;