Only part of the data method is updated at MySQL database update

Requirements: Update the URL in the most characters of '-1 ' to '-5 ', the previous content remains unchanged

The contents of the URL column are as follows:Http://h5game.ecs.cedarmg.com/a/captal/dispther.do?deviceid=b-01-1

SELECT * from Qrmsgwhere url like '%1 '

http://h5game.ecs.cedarmg.com/c/captal/dispther.do?deviceid=b-01- 1

need to put this last -1 Change into -5

Here's how it works:

UPDATE qrmsg SET url= CONCAT (substring_index (URL, '-', ' 2 '), '-5 ') where URL like '%1 '

select* from qrmsg WHERE url like '% 5 '

Http://h5game.ecs.cedarmg.com/ca/capital/dispather.do?deviceid=b-01-5

The following functions are used:

1 , Substring_index (Str,delim,count)

Str: the string to process

Delim: Separators

Count: Count

Example: str=www.google.com

Substring_index (str, '. ', 1)

The result is:www

Substring_index (str, '. ', 2)

The result is:www.google

that is , if count is a positive number, then it is the left-to-right, and the left of the nth delimiter.

Conversely, if it is a negative number, it is All the content from the right to the right of the nth delimiter, such as:

Substring_index (str, '. ',-2)

The result is:google.com

Someone will do for, if I ah in the middle of Google what to do?

very simple, two directions:

1 , right-hand side of the second delimiter, and from the left of the first delimiter of the number:

Substring_index (Substring_index (str, '. ', -2),'. ' , 1);

2 , you know!

2 , concat is to concatenate several strings that can be multiple oh.

Concat (' wo ', ' lin ', ' Xue ', ' bin ')

The result is wolinxuebin.

Reference blog:

http://blog.csdn.net/wolinxuebin/article/details/7845917

Only part of the data method is updated at MySQL database update