SoFunction
Updated on 2025-03-04

Interpretation of string function of MySQL function

MySQL string function

Combine two or more strings into one string

MySQL CONCAT()Functions take one or more string parameters and concatenate them into a string.CONCAT()The function requires at least one parameter, otherwise it will cause an error

Convert all parameters to string type before concatenation. If any parameter isNULL,butCONCAT()Function returnsNULLvalue.

If you want to use a connector, you need to splice the connector between the connected items.

Query statement：select CONCAT(supplier_id,'-',supplier_name) from bms_bills_memo where id = 4
result：6-supplier4

CONCAT_WS()Functions concatenate two or more string values ​​with predefined separators.

The first parameter is the separator, followed by the parameter to be spliced

null is returned only if the delimiter is null, and if the item is null, it will not be spliced.

Query statement：
select CONCAT_WS('-',supplier_id,supplier_name) from bms_bills_memo where id = 4

select CONCAT_WS('-',null,supplier_name) from bms_bills_memo where id = 4

select CONCAT_WS('-',null,null) from bms_bills_memo where id = 4
result：
6-supplier4
supplier4
null

and char_length function

Get the length of a string in bytes and characters

  • length: Get the string length in bytes
  • char_length: Get the length of a string in characters

Byte is a unit of measurement, indicating the amount of data. It is a unit of measurement used by computer information technology to measure storage capacity. Usually, one byte is equal to eight bits.

Characters (Character) letters, numbers, words and symbols used in computers, such as 'A', 'B', '$', '&', etc.

Generally, a letter or character occupies one byte in English, and a Chinese character is represented by two bytes.

  • In the ASCII code, one English letter (in case) is one byte and one Chinese character is two bytes.
  • In UTF-8 encoding, one English word is one byte and one Chinese word is three bytes.
  • In Unicode encoding, one is one byte in English and two bytes in Chinese.
  • Symbol: The English punctuation is one byte and the Chinese punctuation is two bytes. For example: English period . occupies 1 byte in size, Chinese period . occupies 2 bytes in size.
  • In UTF-16 encoding, one English alphabet character or one Chinese character storage requires 2 bytes (some Chinese character storage in the Unicode extension area requires 4 bytes).
  • In UTF-32 encoding, any character in the world needs to be stored 4 bytes.
Query statement：
select LENGTH(supplier_name) from bms_bills_memo  where id = 4

select CHAR_LENGTH(supplier_name) from bms_bills_memo  where id = 4
result：
10
4
Data in the table
supplier_name：supplier4

Use in combination

SELECT postid,
       title,
       IF(CHAR_LENGTH(excerpt) > 20,
          CONCAT(LEFT(excerpt,20), '...'),
          excerpt) summary
FROM posts;

We useIFFunction to check whether the length of the excerpt column is greater than20and use the CONCAT statement toexcerptColumn values ​​and ellipses (...) connect, otherwise just get all excerpts (excerpt)content

Returns the left part of the string with the specified length

Query statement：
select LEFT(client_name,4) from bms_bills_memo where id = 4
result：
Qingdao Free Trade
Data in the table
client_name：Qingdao Free Trade新零售体验中心有限公司

LEFT()The function accepts two parameters:

  • stris the string to extract the substring.
  • lengthis a positive integer that specifies the number of characters to be returned from the left.

LEFT()Function returnsstrThe leftmost length character in the string. ifstrorlengthThe parameters areNULL, then returnNULLvalue.

iflengthfor0or negative, thenLEFTThe function returns an empty string. iflengthGreater thanstrThe length of the string,LEFTThe function returns the entirestrString.

Return to the right part of the string with a specified length

Query statement：
select RIGHT(client_name,4) from bms_bills_memo where id = 4
result：
Limited
Data in the table：
client_name：青岛自贸新零售体验中心Limited

Search and replace substrings in strings.

There are three parameters, which willstringIn-houseold_stringReplace withnew_stringString

Note that when searching for text to be replaced, MySQL uses case-sensitive matching to perform searches for strings to be replaced.

Regular expressions are not supported

Query statement：
select REPLACE(client_name,'Qingdao','Weihai') from bms_bills_memo where id = 4
result：
Weihai Free Trade New Retail Experience Center Co., Ltd.

Query statement：
select REPLACE(bill_of_lading_no,'td','ab') from bms_bills_memo where id = 4
result：
TD10004

result没有发生变化，Because it is case sensitive

Extract a substring from a position with a specific length.

SUBSTRING(string,position);
SUBSTRING(string FROM position);

There are two parameters:

  • stringThe parameter is the string to extract the substring.
  • positionThe parameter is an integer that specifies the start character of the substring.positionCan be a positive or negative integer.

ifpositionIf it is positive, thenSUBSTRINGThe function extracts a substring from the beginning of the string.

ifpositionThe parameter is zero, thenSUBSTRINGThe function returns an empty string.

SQL standard syntax can be used withFROMCall keywords togetherSUBSTRINGfunction

Query statement:
select SUBSTRING(bill_of_lading_no,2) from bms_bills_memo where id = 4
result：
D10004

Query statement：
select SUBSTRING(bill_of_lading_no,-2) from bms_bills_memo where id = 4
result：
04

Data in the table：
bill_of_lading_no:TD10004

If you want to specify the length of the substring to be extracted from the string, you can use the following formSUBSTRINGfunction

SUBSTRING(string,position,length);
SUBSTRING(string FROM position FOR length);
Query statement：
select SUBSTRING(bill_of_lading_no,2,5) from bms_bills_memo where id = 4
result：
D1000

Query statement：
select SUBSTRING(bill_of_lading_no,-7,5) from bms_bills_memo where id = 4
result：
TD100

Data in the table：bill_of_lading_no:TD10004

SUBSTR()The function isSUBSTRING()synonyms for functions, so they can be used interchangeably.

Usage of SUBSTRING_INDEX

Split according to a specific symbol and take out the corresponding value

Query statement：
select SUBSTRING_INDEX('qwe-etr-tyu','-',1)
select SUBSTRING_INDEX('qwe-etr-tyu','-',2)
select SUBSTRING_INDEX('qwe-etr-tyu','-',-1)
select SUBSTRING_INDEX('qwe-etr-tyu','-',-2)

result：
qwe
qwe-etr
tyu
etr-tyu

Remove unwanted characters from the string.

TRIM([{BOTH|LEADING|TRAILING} [removed_str]] FROM str);

You can useLEADING，TRAILINGorBOTHOptions are specifiedTRIM()Functions remove leading, trailing or leading and trailing unnecessary characters from a string.

If you do not specify anything,TRIM()Functions are used by defaultBOTHOptions.

  • [removed_str]is the string to be deleted. By default, it is a space. This means that if a specific string is not specified,TRIM()Functions remove only spaces.
  • strIt is to delete sub-charactersremoved_strstring.
  • TRIM()The function returns a string, deleting unwanted characters.
Query statement：
// Remove leading and trailing spaces from stringSELECT TRIM(' MySQL TRIM Function ');
//Remove only leading spacesSELECT TRIM(LEADING FROM '    MySQL TRIM Function   ');
//Remove only trailing spacesSELECT TRIM(TRAILING FROM '    MySQL TRIM Function   ');
//Delete the line break at the end of the string-- Method one
SELECT 
    TRIM(TRAILING '\n' FROM field_name)
FROM table_name;

-- Method 2
SELECT 
    TRIM(TRAILING '\r' FROM field_name)
FROM table_name;

-- Method Three
SELECT 
    TRIM(TRAILING '\r\n' FROM field_name)
FROM table_name;

If you want to remove only leading or trailing spaces, you can use other string functions:LTRIMandRTRIM

useLTRIMFunction to delete leading spaces of strings

SELECT LTRIM(' MySQL LTRIM function');

useRTRIM()Function to delete trailing spaces of strings

SELECT RTRIM('MySQL RTRIM function ');

8.find_in_set

Find a string in a comma separated list of strings

FIND_IN_SET(needle,haystack);

FIND_IN_SET()The function accepts two parameters:

  • The first parameterneedleis the string to be searched for.
  • The second parameterhaystackis a comma-separated list of strings to search for.

FIND_IN_SET()Function returns an integer or an integer according to the value of the parameterNULLvalue:

  • ifneedleorhaystackforNULL, then the function returnsNULLvalue.
  • ifneedleNot herehaystackIn, orhaystackis an empty string, then zero is returned.
  • ifneedleexisthaystack, a positive integer is returned.

Please note that ifneedleIncludes commas (，), the function will not work properly. In addition, ifneedleis a constant string, andhaystackis a typeSETMySQL will use bit arithmetic optimization.

Query statement：
select FIND_IN_SET('red','yellow,red,blue')
result：
2

Where you can use find_in_set directly

SELECT 
    name, belts
FROM
    divisions
WHERE
    FIND_IN_SET('red', belts);

SELECT 
    name, belts
FROM
    divisions
WHERE
    NOT FIND_IN_SET('black', belts);

The FIND_IN_SET function has the same function as the IN operator

column IN (x, y, z)Expressions andFIND_IN_SET(column, 'x,y,z')same.

INOperators can accept any number of parameters, each separated by commas. but,FIND_IN_SETThe function has only two parameters.

When you want to match values ​​with a list of values ​​in the database, you can useINoperator. And when you want to match the value list stored in the database with commas separated by a comma, you can useFIND_IN_SETfunction.

Format numbers with specific locale settings, rounding to decimal places.

FOMRAT(N,D,locale);

FORMATThe function formats the number N into format, such as"#,###,###.##", round toDdecimal places. It returns a value as a string.

FORMATThe function accepts three parameters:

  • NIt is the number to be formatted.
  • DIt is the number of decimal places to be rounded.
  • localeis an optional parameter to determine the grouping between thousand separators and separators. If omittedlocaleOperator, MySQL will use by defaulten_US。

The following link provides all region names supported by MySQL:/doc/refman/5.7/en/

Query statement：
select format(123456.1258,2)
select format(123456.1248,2)
result：
123,456.13
123,456.12

Two functions can be combined:FORMATandCONCAT。 FORMATFunction rounds the inventory value format to2decimal places. And the concat function adds a RMB symbol at the beginning of the inventory value string (￥)

SELECT 
    productname,
    CONCAT('￥',
            FORMAT(quantityInStock * buyPrice, 2)) stock_value
FROM
    products;

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.