MySQL Database learning Note (v)----MySQL string function, datetime function

First, common String functions:

• 1. Char_length Gets the length (in characters)
• 2. Format formatted
• 3, insert replace the way inserted
• 4. INSTR Get Location
• 5, Left/right take left, take right
• 6. Length Gets the lengths (in bytes)
• 7. Ltrim/rtrim/trim to go space (left/right/Custom)
• 8. STRCMP string comparison
• 9. CONCAT string concatenation
• 10. SUBSTRING string interception

1.char_length: Gets the length (in characters)

Char_length (str)

Explanation: The return value is the length of the string str, the calculated unit is a character (a Chinese is also counted as a character)

Example:

If you are querying the length of a field in a known table, you can write:

2.FORMAT:

FORMAT (X,D)

Explanation: Writes the format of the number x as ' #,###,###.## ', preserves the D-bit after the decimal point in a rounded manner, and returns the result as a string. If D is 0, the result is returned without a decimal point, or with no fractional part.

Example:

3, insert: Replace the way inserted

INSERT (str, POS,len, Newstr)

Explanation: Returns the string str, starting at the POS location of the original STR, replaced with the string newstr, and the length of the replacement is Len.

If the POS exceeds the string length, the return value is the original string. If Len is longer than the length of the subsequent string, it is replaced with the end of the string from position pos.

If any one of the four parameters is null, the return value of the entire function is null.

Example: Replacing a smyh001 with a smyh002

4、INSTR：查找子字符串的位置

INSTR (str, substr)

Explanation: Returns the position of the substring substr in the string str for the first time.

Example:

5.left/right: string interception

• Left Intercept:

 Left (str,len)

Explanation: Returns the string str, starting from the left, the first Len characters.

• Right intercept:

 Right (str,len)

Explanation: Returns the string str, starting from the right, the first Len characters.

6, Length: The length of thestring ( in bytes)

LENGTH (str)

Explanation: The return value is the length of the string str, in bytes. One multibyte character is counted as multibyte. This means that for a string containing five 2-byte characters, the return value of LENGTH () is 10, and the return value of Char_length () is 5.

Note: In UTF-8 encoding, an English character is two bytes, and a Chinese character is three bytes.

7.Ltrim/rtrim/trim Go to Space (left/Right/Custom):

• Remove the left space:

LTRIM (str)

Explanation: Returns the string str, whose boot space character is deleted.

• Remove the middle space:

RTRIM (str)

Explanation: Returns the string str with the trailing space character being deleted.

• Custom Go spaces:

TRIM ([{BOTH | Leading | TRAILING} [Remstr]fromstr

Note: The arguments inside the braces and brackets are optional, all plus, the parameter both is the default.

Explanation: Returns the string str, removing the space. Just go to the beginning (leading) of the space, only the end (TRAILNG) of the space, or at the same time remove the beginning and end of the space line. If none of the classifier both, leadin, or trailing is given, it is assumed to be both.

TRIM ([remstr from] str)

Explanation: The function of the REMSTR keyword is to remove the specified character. That is, all prefixes and/or suffixes that contain remstr will be deleted. If not specified, the space is removed.

Examples of official documents:

8, STRCMP:

STRCMP(expr1,expr2)

Explanation: Determines whether two strings are equal. Returns 0 if all strings are the same, or 1 if the first argument is less than the second, according to the current sorting order; other cases return 1. Similar to the comparator.

Note: The size of the string is compared by ANSI encoding.

Example:

9. CONCAT: string concatenation

CONCAT(str1,str2,...)

Return the result to connect the str1, str2 The string that is generated after stitching. If any one of the arguments is NULL, the return value is null. There may be one or more parameters.

If all parameters are non-binary strings, the result is a non-binary string. If the argument contains any binary string, the result is a binary string. A numeric parameter is automatically converted to the binary string format that is equal to it.

To avoid this situation where the binary is present, use explicit type cast (str) to convert the binary string to a non-binary string. Example: SELECT CONCAT (CAST (Int_col as CHAR), Char_col)

Example: (The Navicat editor automatically converts a binary string to a non-binary string)

10. SUBSTRING: String interception

SUBSTRING (Str,pos)

SUBSTRING (str from POS)

SUBSTRING (Str,pos,len)

SUBSTRING (str from POS for Len)

The format without the Len parameter returns a substring from the string str, starting at position pos. The format with the Len parameter returns a substring of the same length as the Len character from the string str, starting at position pos.

Use the from format as standard SQL syntax.

You may also use a negative value for the POS. In this case, the position of the substring starts at the POS character at the end of the string (that is, the POS is positive, from the left, and the POS is negative, starting from the right).

Examples of official documents are as follows:

Second, the common date and time function:

• 1, DAYOFWEEK, DayOfYear, DayOfMonth
• 2, WEEKDAY
• 3, year, QUARTER, MONTH,
• 4, HOUR, MINUTE, SECOND
• 5, to days, from days
• 6, Curdate, Curtime, now get current time

1, DAYOFWEEK, DayOfYear, DayOfMonth

• Days of the Week: (The day of the week)

DAYOFWEEK(date)

Explanation: Returns the weekday index for date (1= Sunday, 2 = Monday, ..., 7 = Saturday). That is, the range of date corresponds to 1 to 7.

• Find the day of the Year: (Day ordinal of the year)

DAYOFYEAR(date)

Returns the number of days in a year that corresponds to a date, ranging from 1 to 366.

• The day of the one month: (Day of the one month)

DAYOFMONTH(date)

Returns the date corresponding to the month, ranging from 1 to 31. Note: Day (date) and DayOfMonth (date) have the same meaning.

Example:

2, WEEKDAY:

• Days of the Week: (The day of the week)

WEEKDAY(date)

Returns date (0 = Monday, 1 = Tuesday, ... 6 = Sunday) The corresponding working day index. That is, the range of date corresponds to 0 to 6.

Note: DAYOFWEEK (date) and weekday (date) have the same functionality, and the only difference is the range of values that the date corresponds to.

3, year, QUARTER, MONTH

• Year of Evaluation:

YEAR(date)

Returns the date corresponding to the year, ranging from 1000 to 9999.

• Quarterly:

QUARTER(date)

Returns the quarter value of the year in which the date corresponds, ranging from 1 to 4.

• Month: (The month is the first month of the year)

MONTH(date)

Returns the month of date, ranging from 1 to 12.

Example:

4, HOUR, MINUTE, SECOND

Ask for hours:

HOUR(time)

Returns the number of hours corresponding to time. The return value range for the day value is from 0 to 23.

Ask for minutes:

MINUTE(time)

Returns the time corresponding to the number of minutes, ranging from 0 to 59.

To find the number of seconds:

SECOND(time)

Returns the time corresponding to the number of seconds, ranging from 0 to 59.

Example:

5, To_days, from_days

• To ask for a date, a total of many days: (in history)

TO_DAYS(date)

Given a date, returns a number of days, starting from year 0.

• The date of the first day of the history of the river:

FROM_DAYS(N)

Given a number of days, N, returns a date value.

When using From_days () to process an ancient date, it is important to be cautious that he is not used to deal with the number of days before the Gregorian calendar (1582).

6, Curdate, Curtime, now

• For the current month and day:

CURDATE()

Explanation: Returns the current date as a value in the ' YYYY-MM-DD ' or YYYYMMDD format, depending on whether the function is used in a string or in a digital context.

Example:

• To find the current time division seconds:

CURTIME()

Returns the current time in the format ' HH:MM:SS ' or HHMMSS, depending on whether the function is used in a string or in a digital context.

Example:

Ask for the current month day, time division seconds:

NOW()

Example:

Note: If now is placed in the statement, look at the format requirements for the specific field. For example, if it is placed in the Date field, only the month day is displayed, and if it is placed in the DateTime field, the date and time of day and seconds are displayed.

MySQL Database learning Note (v)----MySQL string function, datetime function