The article directories

  • Step 4: Character functions of common functions
  • Common functions:
  • Character functions
    • 1. Length Obtains the number of bytes of the parameter value
    • 2. Concat Concates the string
    • 3. The upper and the lower
      • Example 1: Capitalize the last name, write the first name smaller, and concatenate it
    • 4. The substr, the substring
      • (1) Intercepts all characters after the specified index
      • (2) Intercepts a character with the specified length from the specified index
      • Case 1: The first character of the name is capitalized, the other characters are lowercase and then concatenated with _
    • 5. Instr returns the index of the first occurrence of the substring, or 0 if it cannot be found
    • 6. Trim the front and rear Spaces
    • 7. Lpad implements the left padding of the specified length with the specified character
    • 8. Rpad implements the right fill of the specified length with the specified character
    • 9. Replace: Multiple replacement parameters can be used

Step 4: Character functions of common functions

Concept: Java-like methods that encapsulate a set of logical statements in a method body, exposing the benefits of a method name:1, hiding implementation details2, improve code reuse calls:selectFunction name (argument list) [fromTable 】; Features: ① what is called (function name) ② what is done (function function) classification:1, single-line functions such as concat, length, ifNULL, etc2, grouping function function: do statistics, also known as statistical function, aggregation function, group functionCopy the code
  • The following figure database as an example to write a case

Common functions:

Character function: length: gets the number of bytes (UTF)- 8 -A Chinese character stands for3Bytes, GBK is2Concat substr instr trim upper lower lpad rpad replace mathematical function: round ceil floortruncateMod date function: now curdate Curtimeyear
	month
	monthname
	day
	hour
	minute
	secondStr_to_date date_format Other functions: version databaseuserControl function ifcase
Copy the code

Character functions

1. Length Obtains the number of bytes of the parameter value

SELECT LENGTH('xdr630');
SELECT LENGTH('Xi Moving 630');
Copy the code





The character set used by the client isutf-8A Chinese character is equal to three bytes, so it is 12

Note:

  • View the character set used by the client, which is used hereutf-8.A Chinese character is three bytes and an English character is one byte.
  • If the character set used by the client isgbk.That is, one Chinese character is two bytes, and one English character is one byte.
SHOW VARIABLES LIKE '%char%'
Copy the code

2. Concat Concates the string

  • Name splicing, with an underscore in the middle
SELECT CONCAT(last_name,'_', first_name) nameFROM employees;
Copy the code

3. The upper and the lower

SELECT UPPER('xdr630');
SELECT LOWER('MIKE');
Copy the code



Example 1: Capitalize the last name, write the first name smaller, and concatenate it

SELECT CONCAT(UPPER(last_name),LOWERName (first_name))FROM employees;
Copy the code

4. The substr, the substring

Note: The index starts at 1

(1) Intercepts all characters after the specified index

SELECT SUBSTR('Li Mochou falls in love with Lu Zhanyuan'.7)  out_put;
Copy the code

(2) Intercepts a character with the specified length from the specified index

  • The first argument here indicates the start from the first index, and the second argument indicates the length of the character
SELECT SUBSTR('Li Mochou falls in love with Lu Zhanyuan'.1.3) out_put;
Copy the code

Case 1: The first character of the name is capitalized, the other characters are lowercase and then concatenated with _

SELECT CONCAT(UPPER(SUBSTR(last_name,1.1)),'_'.LOWER(SUBSTR(last_name,2)))  out_put
FROM employees;
Copy the code

SELECT LENGTH(TRIM(‘ TRIM ‘)) AS out_put;

5. Instr returns the index of the first occurrence of the substring, or 0 if it cannot be found

SELECT INSTR('Yang Buyin regrets falling in love with Yin Six'.The Old Six) AS out_put;
Copy the code

SELECT INSTR('Yang Buyin regrets falling in love with Yin Six'.'The Yin Eight') AS out_put;
Copy the code

6. Trim the front and rear Spaces

SELECT TRIM('Zhang Cuishan') AS out_put;
Copy the code

SELECT LENGTH(TRIM('Zhang Cuishan')) AS out_put;
Copy the code

  • Remove the a parameter before and after
SELECT TRIM('a' FROM 'aaaaaaaaa aaaaaaaaaaaa cui zhang mountain aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')  AS out_put;
Copy the code

  • I’m going to double the a’s and get rid of it, and I’m going to end up with an extra A
SELECT TRIM('aa' FROM 'aaaaaaaaa aaaaaaaaaaaa cui zhang mountain aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')  AS out_put;
Copy the code

7. Lpad implements the left padding of the specified length with the specified character

  • with*Fill the left side with 10 characters
SELECT LPAD('Yin Su'.10.The '*') AS out_put;
Copy the code

  • If the length of the fill is less than the length of the character, return the original character
SELECT LPAD('Yin Su'.2.The '*') AS out_put;
Copy the code

8. Rpad implements the right fill of the specified length with the specified character

SELECT RPAD('Yin Su'.12.'ab') AS out_put;
Copy the code

9. Replace: Multiple replacement parameters can be used

SELECT REPLACE('Zhou Zhi If Zhou Zhi if Zhou Zhi if Zhang Wuji fell in love with Zhou Zhi if'.'Zhou Zhiruo'.'zhao') AS out_put;
Copy the code