Detailed explanation of string functions in MySQL-Mysql Tutorial-php.cn

Detailed explanation of string functions in MySQL

零下一度

Release： 2017-07-17 11:01:44

Original

1477 people have browsed it

I saw a question about mysql in the forum today. The question is as follows

good_id cat_id
12654 665,569
12655 601,4722

goods_id is the product id
cat_id is the classification id
How do I check the data based on this classification ID (a product has multiple categories, separated by commas)
I am using like now. In this case, entering a classification id is This product can be found by entering 688 or 4722, but the product can also be found by inputting 722.

If you use like, there will definitely be problems. My initial idea is to replace the string in cat_id with an array, so that the in operation in mysql can be used. In this way, search 722 will not appear, and all products under the 4722 category will appear. I searched for a long time on the Internet, but I couldn't find any string operation function in this area. However, I found that although the find_in_set function cannot convert a string into an array, the above situation will not occur. I found that there were many functions that I didn't know, so I collected them from the manual and the Internet for a long time and made some examples.

CHAR_LENGTH(str) Returns the number of characters in str

##LENGTH(str) Returns the byte length of str

CONCAT(str1,str2....) Returns the concatenation of parameters str1, str2...., if there is a null value, it returns null

CONCAT_WS (x, str1, str2...) ---concat with separator The first parameter x represents the separator, and the following parameters are the string

If x is NULL, NULL is returned, if the string has NULL Will be ignored

INSERT(s1,x,len,s2) String replacement s1 target string x is the starting position of s1 (the subscript starts from 1) len is The length s2 after x is the string that needs to be replaced

If x exceeds the length subscript of s1, return s1. If len is greater than the remaining length of the string after x, replace all

LOWER(str), LCASE(str) The string is converted to lower case

UPPER(str), UCASE(str) The string is converted to Uppercase

LEFT(str,n) Returns the leftmost n characters of the string str

RIGHT(str ,n) Returns the rightmost n characters of string str

LPAD(s1,len,s2) Returns s2 filled from the left side of s1 until the total length is len, if If len

RPAD(s1,len,s2) Returns s2 and will be filled from the right of s1. Until the length of len, if len

LTRIM(S) Remove the spaces on the left RTRIM(s) Delete the spaces on the right TRIM(s) Delete the spaces on both sides

TRIM(s1 FROM s2) Delete the s1 string appearing at both ends of s2

##REPEAT(s,n) returns a string composed of n s, n<=0, returns empty; s or n is NULL, returns NULL

SPACE(n) Returns n space strings

REPLACE(s,s1,s2) Returns --replacing s with s2 The result of all s1 strings

##STRCPM(s1,s2) s1==s2 returns 0 s1s2 returns 1

SUBSTRING(s,n,len), MID(s,n,len) returns a string of len length starting from n. If n<0, it starts from the last reciprocal of s. Start intercepting n characters. If n=0, an empty string will be returned; len can be omitted - all characters after n will be intercepted

LOCATE(str1, str2), POSITION(str1 IN str2), INSTR(str2, str1) Return the starting position of str1 in str2

REVERSE(s) Returns the reverse of the string of s

ELT(n,str1,str2, str3...) Returns the nth string in str1, str2.., if n exceeds return null

FIELD(s,str1,str2,str3.. ..) Returns the position of the first occurrence of s in str1, str2..., without returning 0

FIND_IN_SET(s,str1) Returns the position of s in The position of occurrence in str1, where str1 consists of multiple strings, a comma-separated list

The above is the detailed content of Detailed explanation of string functions in MySQL. For more information, please follow other related articles on the PHP Chinese website!