Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "the specific usage of string function of MYSQL". Many people will encounter such a dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

For operations on string positions, the first position is marked as 1.

ASCII (str)

Returns the ASCII code value of the leftmost character of the string str. Returns 0 if str is an empty string. Returns NULL if str is NULL.

Mysql > select ASCII ('2')

-> 50

Mysql > select ASCII (2)

-> 50

Mysql > select ASCII ('dx')

-> 100

You can also see the ORD () function.

ORD (str)

If the leftmost character of the string str is a multibyte character, by using the format ((first byte ASCII code) * 256 + (second byte ASCII code)) [* 256+third byte ASCII code...] Returns the ASCII code value of the character to return the multibyte character code. If the leftmost character is not a multibyte character. Returns the same value as the ASCII () function.

Mysql > select ORD ('2')

-> 50

CONV (NParth from Basebook Basebook totalbase)

Convert numbers between different digital bases. Returns the string number of the number N, transformed from the from_base base to the to_ base, and NULL if any parameter is NULL. The parameter N is interpreted as an integer, but can be specified as an integer or a string. The minimum base is 2 and the maximum base is 36. If to_base is a negative number, N is considered to be a signed number, otherwise N is regarded as an unsigned number. CONV works at 64-bit precision.

Mysql > select CONV ("a", 16pm 2)

-> '1010'

Mysql > select CONV ("6e", 18penny 8)

-> '172'

Mysql > select CONV (- 17, 10, 10, 14, 18)

->'- H'

Mysql > select CONV (10 + "10" +'10)

-> '40'

BIN (N)

Returns a string representation of the binary value N, where N is a long integer (BIGINT) number, which is equivalent to CONV. If N is NULL, return NULL.

Mysql > select BIN (12)

-> '1100'

OCT (N)

Returns a string representation of the octal value N, where N is a long integer number, which is equivalent to CONV. If N is NULL, return NULL.

Mysql > select OCT (12)

-> '14'

HEX (N)

Returns a string representation of the hexadecimal value N, where N is a BIGINT number, which is equivalent to CONV. If N is NULL, return NULL.

Mysql > select HEX (255)

-> 'FF'

CHAR (NJI...)

CHAR () interprets parameters as integers and returns a string consisting of the ASCII code characters of those integers. The NULL value is skipped.

Mysql > select CHAR (77, 121, 83, 81, 76)

-> 'MySQL'

Mysql > select CHAR (77, 7. 3, 7. 3, 7. 3)

-> 'MMM'

CONCAT (str1,str2,...)

Returns the string from the parameter link. Returns NULL if any parameter is NULL. There can be more than 2 parameters. A numeric parameter is transformed into an equivalent string.

Mysql > select CONCAT ('My',' slots, 'QL')

-> 'MySQL'

Mysql > select CONCAT ('My', NULL,' QL')

-> NULL

Mysql > select CONCAT (14.3)

-> '14.3'

LENGTH (str)

OCTET_LENGTH (str)

CHAR_LENGTH (str)

CHARACTER_LENGTH (str)

Returns the length of the string str.

Mysql > select LENGTH ('text')

-> 4

Mysql > select OCTET_LENGTH ('text')

-> 4

Note that for multibyte characters, their CHAR_LENGTH () is evaluated only once.

LOCATE (substr,str)

POSITION (substr IN str)

Returns the position where the substring substr appears first in the string str, and returns 0. 0 if substr is not in str.

Mysql > select LOCATE ('bar',' foobarbar')

-> 4

Mysql > select LOCATE ('xbar',' foobar')

-> 0

This function is multibyte reliable.

LOCATE (substr,str,pos)

Returns the position where the substring substr appears first in the string str, starting with the position pos. If substr is not in str, return 0.

Mysql > select LOCATE ('bar',' foobarbar',5)

-> 7

This function is multibyte reliable.

INSTR (str,substr)

Returns the first occurrence of the substring substr in the string str. This is the same as LOCATE (), which has the form of two parameters, except that the parameters are reversed.

Mysql > select INSTR ('foobarbar',' bar')

-> 4

Mysql > select INSTR ('xbar',' foobar')

-> 0

This function is multibyte reliable.

LPAD (str,len,padstr)

Returns the string str, padded on the left with the string padstr until str is len characters long.

Mysql > select LPAD ('hi',4,'??')

->'?? hi'

RPAD (str,len,padstr)

Returns the string str, padded on the right with the string padstr until str is len characters long.

Mysql > select RPAD ('hi',5,'?')

-> 'hi???'

LEFT (str,len)

Returns the leftmost len characters of the string str.

Mysql > select LEFT ('foobarbar', 5)

-> 'fooba'

This function is multibyte reliable.

RIGHT (str,len)

Returns the rightmost len characters of the string str.

Mysql > select RIGHT ('foobarbar', 4)

-> 'rbar'

This function is multibyte reliable.

SUBSTRING (str,pos,len)

SUBSTRING (str FROM pos FOR len)

MID (str,pos,len)

Returns a substring of len characters from the string str, starting with the position pos. The variant form of using FROM is the ANSI SQL92 syntax.

Mysql > select SUBSTRING ('Quadratically',5,6)

-> 'ratica'

This function is multibyte reliable.

SUBSTRING (str,pos)

SUBSTRING (str FROM pos)

Returns a substring from the starting position of the string str, pos.

Mysql > select SUBSTRING ('Quadratically',5)

-> 'ratically'

Mysql > select SUBSTRING ('foobarbar' FROM 4)

-> 'barbar'

This function is multibyte reliable.

SUBSTRING_INDEX (str,delim,count)

Returns the substring after the delimiter delim that appears at the count of the string str. If count is positive, returns all characters from the last delimiter to the left (the number from the left). If count is negative, returns the last delimiter to all characters on the right (from the right).

Mysql > select SUBSTRING_INDEX ('www.mysql.com','. 2)

-> 'www.mysql'

Mysql > select SUBSTRING_INDEX ('www.mysql.com','.'- 2)

-> 'mysql.com'

This function is reliable for multibytes.

LTRIM (str)

Returns the string str whose leading space character is deleted.

Mysql > select LTRIM ('barbar')

-> 'barbar'

RTRIM (str)

Returns the string str whose trailing space character was deleted.

Mysql > select RTRIM ('barbar')

-> 'barbar'

This function is reliable for multibytes.

TRIM ([[BOTH | LEADING | TRAILING] [remstr] FROM] str)

Returns the string str whose remstr prefixes or suffixes are deleted. If no modifiers BOTH, LEADING, or TRAILING are given, BOTH is assumed. If remstr is not specified, the space is deleted.

Mysql > select TRIM ('bar')

-> 'bar'

Mysql > select TRIM (LEADING 'x' FROM 'xxxbarxxx')

-> 'barxxx'

Mysql > select TRIM (BOTH 'x' FROM 'xxxbarxxx')

-> 'bar'

Mysql > select TRIM (TRAILING 'xyz' FROM' barxxyz')

-> 'barx'

This function is reliable for multibytes.

SOUNDEX (str)

Returns a homonym string for str. Two strings that sound "roughly the same" should have the same homonym string. A "standard" homophone string is four characters long, but the SOUNDEX () function returns a string of any length. You can use SUBSTRING () on the result to get a "standard" homophone string. All non-numeric alphanumeric characters are ignored in the given string. All international letters outside Amurz are treated as vowels.

Mysql > select SOUNDEX ('Hello')

-> 'H400'

Mysql > select SOUNDEX ('Quadratically')

-> 'Q36324'

SPACE (N)

Returns a string of N space characters.

Mysql > select SPACE (6)

->''

REPLACE (str,from_str,to_str)

Returns the string str where all occurrences of the string from_str are replaced by the string to_str.

Mysql > select REPLACE ('www.mysql.com',' walled, 'Ww')

-> 'WwWwWw.mysql.com'

This function is reliable for multibytes.

REPEAT (str,count)

Returns a string consisting of the string str that repeats countTimes times. If count select REPEAT ('MySQL', 3)

-> 'MySQLMySQLMySQL'

REVERSE (str)

Returns the string str that reverses the order of characters.

Mysql > select REVERSE ('abc')

-> 'cba'

This function is reliable for multi-bytes.

INSERT (str,pos,len,newstr)

Returns the string str, the substring at the beginning of the pos and the length of the len character is replaced by the string newstr.

Mysql > select INSERT ('Quadratic', 3,4,' What')

-> 'QuWhattic'

This function is reliable for multibytes.

ELT (NMagol str1 ~ str2 ~ str3 ~..)

If N = 1, return str1, if N = 2, return str2, and so on. Returns NULL if N is less than 1 or greater than the number of parameters. ELT () is the inverse operation of FIELD ().

Mysql > select ELT (1, 'ej',' Heja', 'hej',' foo')

-> 'ej'

Mysql > select ELT (4, 'ej',' Heja', 'hej',' foo')

-> 'foo'

FIELD (str,str1,str2,str3,...)

Returns str in str1, str2, str3,... The index of the list. If str is not found, 0 is returned. FIELD () is the inverse operation of ELT ().

Mysql > select FIELD ('ej',' Hej', 'ej',' Heja', 'hej',' foo')

-> 2

Mysql > select FIELD ('fo',' Hej', 'ej',' Heja', 'hej',' foo')

-> 0

FIND_IN_SET (str,strlist)

If the string str is in the table strlist consisting of N substrings, a value from 1 to N is returned. A string table is a string of substrings separated by ",". If the first argument is a constant string and the second argument is a column of type SET, the FIND_IN_SET () function is optimized to use bit operations! Returns 0 if str is not in strlist or if strlist is an empty string. Returns NULL if any of the parameters is NULL. If the first parameter contains a ",", the function will not work properly.

Mysql > SELECT FIND_IN_SET ('bachelors, page1, pr, pas, c, d')

-> 2

MAKE_SET (bits,str1,str2,...)

Returns a collection containing a string of substrings separated by "," characters, consisting of strings corresponding to bits in the bits collection. Str1 corresponds to bit 0, corresponding to bit 1, and so on. In str1, str2,... The NULL string in is not added to the result.

Mysql > SELECT MAKE_SET (1, recording, writing, etc.)

->'a'

Mysql > SELECT MAKE_SET (1 | 4 people hellogged, girls, niceboys, girls, worlds)

-> 'hello,world'

Mysql > SELECT MAKE_SET (0m, m, m).

->''

EXPORT_SET (bits,on,off, [separator, [number_of_bits]])

Returns a string, where you get a "on" string for each bit set in "bits" and a "off" string for each reset bit. Each string is separated by "separator" (default ","), and only the "number_of_bits" (default 64) bit of "bits" is used.

Mysql > select EXPORT_SET (5)

-> Y ~ (1) N ~ (1) N

LCASE (str)

LOWER (str)

Returns the string str, which changes all characters to lowercase based on the current character set mapping (default is ISO-8859-1 Latin1). This function is reliable for multibytes.

Mysql > select LCASE ('QUADRATICALLY')

-> 'quadratically'

UCASE (str)

UPPER (str)

Returns the string str, changing all characters to uppercase based on the current character set mapping (default is ISO-8859-1 Latin1). This function is reliable for multibytes.

Mysql > select UCASE ('Hej')

-> 'HEJ'

This function is reliable for multibytes.

LOAD_FILE (file_name)

Reads the file and returns the contents of the file as a string. The file must be on the server, you must specify the full pathname to the file, and you must have file permission. Everything in the file must be readable and less than max_allowed_packet. If the file does not exist or cannot be read for one of the above reasons, the function returns NULL.

Mysql > UPDATE table_name

SET blob_column=LOAD_FILE ("/ tmp/picture")

WHERE id=1

MySQL automatically converts numbers to strings if necessary, and vice versa:

Mysql > SELECT 1 + "1"

-> 2

Mysql > SELECT CONCAT (2 test')

->'2 test'

If you want to explicitly convert a number to a string, pass it as an argument to CONCAT ().

If the string function provides a binary string as an argument, the resulting string is also a binary string. A number that is converted to a string is treated as a binary string. This only affects the comparison.

IFNULL (expr1,expr2)

If expr1 is not NULL,IFNULL () returns expr1, otherwise it returns expr2. IFNULL () returns a number or string value, depending on the context in which it is used.

Mysql >; select IFNULL (1J 0)

->; 1

Mysql >; select IFNULL (0jre 10)

->; 0

Mysql >; select IFNULL (1compo 0jol 10)

->; 10

Mysql >; select IFNULL (1pm 0penny yes`)

->; 'yes'

IF (expr1,expr2,expr3)

If expr1 is TRUE (expr1;0 and expr1;NULL), then IF () returns expr2, otherwise it returns expr3. IF () returns a number or string value, depending on the context in which it is used.

Mysql >; select IF (1 >; 2penny 2jin3)

->; 3

Mysql >; select IF (1; 'yes'

Mysql >; select IF (strcmp ('test','test1'),' yes','no')

->; 'no'

Expr1 is calculated as an integer value, which means that if you are testing floating-point or string values, you should use a comparison operation to do so.

Mysql >; select IF (0.1pm 1pm 0)

->; 0

Mysql >; select IF (0.1th 0pm 1pm 0)

->; 1

In the first case above, IF (0. 1) returns 0 because 0. 1 is converted to an integer value, causing IF (0) to be tested. This may not be what you expect. In the second case, the comparison tests the original floating-point value to see if it is non-zero, and the result of the comparison is used as an integer.

CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result...] [ELSE result] END

CASE WHEN [condition] THEN result [WHEN [condition] THEN result...] [ELSE result] END

The first version returns result, where value=compare-value. In the second version, result is returned if the first condition is true. If there is no matching result value, the result is returned in the result after ELSE. If there is no ELSE part, then NULL is returned.

Mysql >; SELECT CASE 1 WHEN 1 THEN "one" WHEN 2 THEN "two" ELSE "more" END

>; "one"

Mysql >; SELECT CASE WHEN 1 >; 0 THEN "true" ELSE "false" END

>; "true"

Mysql >; SELECT CASE BINARY "B" when "a" then 1 when "b" then 2 END

->; NULL

This is the end of the content of "specific usage of string function of MYSQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.