MySQL segmented replacement string and concat splicing string to null solution

trim removes the specified characters and commas in the string:

Requirement background:

Insert picture description here


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.

Insert picture description here


At this time, the traditional concat cannot be spliced, because the CONCAT() function has NULL in the splicing parameter and returns NULL directly.

Insert picture description here


Solution: concat_ws() function

UPDATE percent SET  name = CONCAT_WS(',', name,NULL,'564');
Insert picture description here

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.