Common MySQL string processing functions

MySQL string processing functions are our most commonly used functions. The MySQL string processing functions described below are used to convert string cases and clear spaces at the beginning and end of a string, we hope that you will have a better understanding of MySQL string processing functions.

MySQL string case-sensitive Conversion

DeMySQL string case-sensitive conversion functions include lower (), uppper (), lcase (), and ucase ()

 
 

  
  
mysql> select lower('DDD');  
  
  
+--------------+  
  
  
| lower('DDD') |  
  
  
+--------------+  
  
  
|   ddd |  
  
  
+--------------+  
  
  
mysql> select upper('ddd');    
  
  
+--------------+  
  
  
| upper('ddd') |  
  
  
+--------------+  
  
  
| DDD |  
  
  
+--------------+  
  
  
mysql> select lcase('DDD');    
  
  
+--------------+  
  
  
| lcase('DDD') |  
  
  
+--------------+  
  
  
| ddd |  
  
  
+--------------+  
  
  
mysql> select ucase('ddd');  
  
  
+--------------+  
  
  
| ucase('ddd') |  
  
  
+--------------+  
  
  
| DDD |  
  
  
+--------------+  
 
 

In general, I select lower () and upper () to convert string case, because it is compatible with functions in other databases.

Removes spaces at the beginning and end of a string.

In MySQL, there are three spaces at the beginning and end of the MySQL string: ltrim (), rtrim (), trim ()

 
 

  
  
mysql> select concat('.', ltrim(' ddd '), '.');  
  
  
+----------------------------------+  
  
  
| concat('.', ltrim(' ddd '), '.') |  
  
  
+----------------------------------+  
  
  
| .ddd . |  
  
  
+----------------------------------+  
  
  
mysql> select concat('.', rtrim(' ddd '), '.');  
  
  
+----------------------------------+  
  
  
| concat('.', rtrim(' ddd '), '.') |  
  
  
+----------------------------------+  
  
  
| . ddd. |  
  
  
+----------------------------------+  
  
  
mysql> select concat('.', trim(' ddd '), '.');  
  
  
+---------------------------------+  
  
  
| concat('.', trim(' ddd '), '.') |  
  
  
+---------------------------------+  
  
  
| .ddd. |  
  
  
+---------------------------------+   
 
 

The trim string function in MySQL is really powerful. It not only removes spaces at the beginning and end of a string, but also removes any specified characters. Ltrim (), rtrim () is only a subset of its functions. Let's take a look at the complete syntax of the trim function:

 
 

  
  
trim([{both | leading | trailing} [remstr] from] str)2. trim([remstr from] str)  
  
  
 
 
 

1. Clear the MySQL string header characters.

 
 

  
  
mysql> select trim(leading '.' from '..ddd..');  
  
  
+----------------------------------+  
  
  
| trim(leading '.' from '..ddd..') |  
  
  
+----------------------------------+  
  
  
| ddd.. |  
  
  
+----------------------------------+   
  
  
 
 
 

2. Clear the tail character of the string.

 
 

  
  
mysql> select trim(trailing '.' from '..ddd..');  
  
  
+-----------------------------------+  
  
  
| trim(trailing '.' from '..ddd..') |  
  
  
+-----------------------------------+  
  
  
| ..ddd |  
  
  
+-----------------------------------+  
  
  
 
 
 

3. Clear the first and end characters of the string.

 
 

  
  
mysql> select trim(both '.' from '..ddd..');  
  
  
+-------------------------------+  
  
  
| trim(both '.' from '..ddd..') |  
  
  
+-------------------------------+  
  
  
| ddd |  
  
  
+-------------------------------+  
  
  
mysql> select trim('.' from '..ddd..');  
  
  
+--------------------------+  
  
  
| trim('.' from '..ddd..') |  
  
  
+--------------------------+  
  
  
| ddd |  
  
  
+--------------------------+  
 
 

Trim () removes spaces at the beginning and end of the MySQL string by default.

 

MySQL string segmentation implementation

Replace string with the MySQL replace Function

How to quickly enable MySQL slow log query

How to query duplicate records in MYSQL

How to Implement MySQL full-text Query