trim removes the specified characters and commas in the string:
To delete 1637 in the name field;
using replace to replace with empty will cause the problem of remaining commas.
Solution: trim function
update `percent` set name = trim(both ',' from replace(concat(',', name, ','), ',1637,', ','));
The meaning of the statement is:
First splicing two commas at the beginning and end of the name field becomes: ,1637,4512,12,564, and then use replace to replace ,1637,
with a comma, and the string becomes: ,4512,12,564, and then use trim(both from )
Remove the comma at the beginning and the end to delete the specified characters and commas. The logic of removing the same characters at the beginning and end or any paragraph in the middle.
The complete syntax is: TRIM (both characters to be deleted from the source string)
Example: TRIM(BOTH'x' FROM'xxxbarxxx'); --delete the specified first and last characters
When concat concatenates strings, the field is null. Solution:
Requirement background: The
above implementation uses trim to delete the specified characters in the field, and these characters are all spliced together. Then when the field is spliced with care for the first time, that is, when the field is null, the spliced characters cannot be spliced.
At this time, the traditional concat cannot be spliced, because the CONCAT() function has NULL in the splicing parameter and returns NULL directly.
Solution: concat_ws() function
UPDATE percent SET name = CONCAT_WS(',', name,NULL,'564');
There is no need to consider the problem of commas when using this function, because as long as it is not null, each splicing will automatically splice a comma in front of the character, and the splicing will not be spliced when it is null for the first time.
The difference between CONCAT_WS (separator, parameter 1, parameter 2, ... parameter n) and CONCAT (parameter 1, parameter 2, ... parameter n)
CONCAT_WS(): means concat with separator, that is, string connection with separator
CONCAT(): means to concatenate strings
The most important difference between the two is that in the CONCAT() function, there is NULL in the splicing parameter and returns NULL directly, while the CONCAT_WS() function does not return NULL because of the NULL value.