“This is the 18th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021”

trim

Trim (STR) is generally used to remove Spaces before and after data.

If you need to delete the specified string before and after, the format is trim(remstr FROM STR)

Trim also provide a parameter to limit delete + | | preceding] before [the specified string

ltrim/rtrim

Ltrim (STR), rtrim(STR) if you only need to remove Spaces before (or after) a string, you can use mysql’s simplified trim wrapping functions.

trim(leading ' ' from str) ==> ltrim(str)
trim(trailing ' ' from str) ==> rtrim(str)
Copy the code

find_in_set

find_in_set(str, strList)
Copy the code

When a string is a partitioned list of data, that is, the string can be sliced to form a list of data, in which case find_in_set can be used to find if a substring is in that list, if it is, return a numeric substring (starting at 1), if it is not found, return 0

format

In general, we use the aggregation function AVG in the query to calculate the average value, which is likely to result in a decimal number, sometimes requiring that the value be reserved for several decimal places and separated by thousandths

format(N, D, local)
Copy the code

N indicates the value to be processed, D indicates the number of decimal places to be reserved, and local indicates the voice area. The default value is en_US, that is,xx, XXX,xxx.xx

lcase/ucase

Mysql provides a function to change the case of a string:

  • lcaseString to lowercase
  • ucaseUppercase string
SELECT lcase('AAA');
--> aaa
SELECT ucase('aaa');
--> AAA
Copy the code

repeat

SELECT repeat(str, n)
Copy the code

Returns the string STR repeated n times

lpad/rpad

Fills the left or right side of the string with specified characters to make the string length reach the specified length

In the most common scenario, we need to generate a serial number of fixed length, for example, the serial number format is limited to 5 digits, and the number of digits is not enough to fill with 0.

A lazy way to do this is to just start at 1, so I don’t run out of digits

Another way is to use the Lpad provided by mysql

lpad(str, length, repeatStr);
rpad(str, length, repeatStr);
Copy the code

STR indicates the character to be processed, length indicates the character length after processing, and repeatStr indicates the character to be filled

Now for a tricky Angle, let’s say I fill 1 with ABC to fill 5 bits, how does that fill?

As shown in the test, the direct loop is filled with the string ABC and terminates after filling enough digits