SQL intercept field last content statement separated by a specific character

Source: Internet
Author: User
Keywords Web programming MySQL Tutorial
Tags code content data default function jpg mysql mysql tutorial

This article introduces a MySQL batch intercept the last separated part of the SQL statement to another field to have a friend in need of reference.
-
-
Field images saved data is: a.jpg;b.jpg;c.jpg

To assign the last picture of images c.jpg to Images_default

The executed SQL statement is

The code is as follows
UPDATE product SET ' image_default ' = REVERSE (MID REVERSE (' images '), 1,locate ('; ', REVERSE (Images))-1) WHERE product_id > 1

Here we take advantage of a few functions to refer to

REVERSE

REVERSE (str)
Returns the string str of the reversed character order.

The code is as follows
Mysql> Select REVERSE (' abc ');
-> ' CBA '

This function is reliable for multiple bytes.

MID

MID () function

The MID function is used to extract characters from a text field.
SQL MID () syntax

The code is as follows
SELECT MID (Column_name,start[,length]) from table_name

LOCATE

LOCATE (SUBSTR,STR), LOCATE (Substr,str,pos)
The first syntax returns the first occurrence of the string substr of the str neutron string. The second syntax returns the first occurrence of the string str neutron string substr, starting at the Pos point. If SUBSTR is not in Str, the return value is 0

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.