MySQL 常见str函数

MySQL常见的字符串函数整理自官档。SUBSTR(str,pos), SUBSTR(str FROM pos),SUBSTR(str,pos,len), SUBSTR(str FROMpos FOR len)SUBSTR() is a synonym for SUBSTRING(). SUBSTRING(str,pos), SUBSTRING(str FROM pos),SUBSTRING(str,pos,len),SUBSTRING(str FROM pos FOR len)The forms without a len argument return asubstring from string str starting at position pos.The forms with a len argument return asubstring len characters long from string str, starting atposition pos. The forms that use FROM arestandard SQL syntax. It is also possible to use a negativevalue for pos. In this case, the beginningof the substring is pos characters from the end of thestring, rather than the beginning. Anegative value may be used for pos in any of the forms of thisfunction.For all forms of SUBSTRING(), the positionof the first character in the string from which thesubstring is to be extracted is reckoned as1.mysql> SELECTSUBSTRING(‘Quadratically’,5);-> ‘ratically’mysql> SELECT SUBSTRING(‘foobarbar’ FROM4);-> ‘barbar’mysql> SELECTSUBSTRING(‘Quadratically’,5,6);-> ‘ratica’mysql> SELECT SUBSTRING(‘Sakila’, -3);-> ‘ila’mysql> SELECT SUBSTRING(‘Sakila’, -5,3);-> ‘aki’mysql> SELECT SUBSTRING(‘Sakila’ FROM -4FOR 2);-> ‘ki’This function is multibyte safe.If len is less than 1,the result is the empty string. SUBSTRING_INDEX(str,delim,count)Retur开发云主机域名ns the substring from string strbefore count occurrences of the delimiter delim. If countis positive, everything to the left of thefinal delimiter (counting from the left) is returned. If countis negative, everything to the right of thefinal delimiter (counting from the right) is returned.SUBSTRING_INDEX() performs a case-sensitivematch when searching for delim.mysql> SELECTSUBSTRING_INDEX(‘www.mysql.com’, ‘.’, 2);-> ‘www.mysql’mysql> SELECTSUBSTRING_INDEX(‘www.mysql.com’, ‘.’, -2);-> ‘mysql.com’This function is multibyte safe.CHAR_LENGTH(str) 返回字符数Returns the length of the string str,measured in characters. A multibyte character counts as asingle character. This means that for astring containing five 2-byte characters, LENGTH() returns10, whereas CHAR_LENGTH() returns 5.LENGTH(str) 返回字节数Returns the length of the string str,measured in bytes. A multibyte character counts as multiplebytes. This means that for a stringcontaining five 2-byte characters, LENGTH() returns 10, whereasCHAR_LENGTH() returns 5.mysql> SELECT LENGTH(‘text’);

相关推荐: SQL获取字段字符串中文首字母

有中文有英文，获取中文首字母信息。SELECT person_name_cn ,ELT(INTERVAL(CONV(HEX开发云主机域名(LEFT(CONVERT(person_name_cn USING gbk),1)),16,10),0xB0A1,0xB0…