Mysql string processing function packing

Mysql string processing functions ???????? The following are some of the methods used to sort MySql string functions, which are listed for reference only. 1. Invalid Data replacement function: replace (str, from_str, to_str )??????? After the query, it is found that some illegal data exists in the Database, which makes it very ugly. when the data is stored in the database, only the leading and trailing spaces are checked.

How to sort Mysql string processing functions? ??????? The following are some of the methods used to sort MySql string functions, which are listed for reference only. 1. Invalid Data replacement function: replace (str, from_str, to_str )??????? After the query, it is found that some illegal data exists in the Database, which makes it very ugly. when the data is stored in the database, only the leading and trailing spaces are checked.

Sort Mysql string processing functions

?

??????? The following are some of the methods used to sort MySql string functions, which are listed for reference only.

1,Invalid Data replacement

Function:Replace (str, from_str, to_str)
??????? After the query, it is found that some illegal data exists in the Database, which makes it very ugly. when the data is stored in the database, only the leading and trailing spaces are checked. No more strict space removal is performed, and only post-processing can be performed.

        update table set cname = replace(cname,'　','');        update table set cname = replace(cname,' ','');

?

??????? Some fields look like spaces, but they are not actually spaces. This field may be in full-width or other format. Copy the field and paste it into the replacement file.

?

2,Query Result field connection
??????? Function 1:Concat (str1, str2, str3)
??????? Function 2:Concat_ws (splitstr, str1, str2, str3)
??????? Function 3:Group_concat (str)

?

Function 1Concat (str1, str2, str3)
??????? Simple, just concatenate the columns or strings and use them.

?

Function 2Concat_ws (splitstr, str1, str2, str3)?
??????? Concatenate str1, str2, and str3 with splitstr as the separator and return the result.

?

Function 3?Group_concat (str)?
??????? Manual Description: This function returns a string with a non-NULL value from a group of connections.
??????? It is abstract and hard to understand.

??????? Group_concat () calculates which rows belong to the same group and displays the columns belonging to the same group. The columns to be returned are determined by the function parameter (that is, the field name. The group must have a standard, that is, grouping based on the columns specified by group.

1. Test statement,
????????

select province,group_concat(name) from city group by province;

??????? Hebei ??? Baoding, Shijiazhuang, Handan, Xingtai
??????? Beijing ??? Beijing
????????

select group_concat(name) from city;

??????? Baoding, Shijiazhuang, Handan, Xingtai, Beijing

?

Function 4SubString_index (str, indexstr, index)
??????? A string truncation function. The second parameter is a specified character, and the third parameter is the number of occurrences of the character.
????????

select sum(substring_index("0.124M",'M',1)) from dual;

??????? The returned result is 0.124.

?

?