字符函数
大小写转换
mysql> select employee_id,upper(concat(first_name,' ',last_name)) as full_name from employees limit 0,10;
+-------------+------------------+
| employee_id | full_name |
+-------------+------------------+
| 100 | STEVEN K_ING |
| 101 | NEENA KOCHHAR |
| 102 | LEX DE HAAN |
| 103 | ALEXANDER HUNOLD |
| 104 | BRUCE ERNST |
| 105 | DAVID AUSTIN |
| 106 | VALLI PATABALLA |
| 107 | DIANA LORENTZ |
| 108 | NANCY GREENBERG |
| 109 | DANIEL FAVIET |
+-------------+------------------+
10 rows in set (0.06 sec)
mysql> select employee_id,lower(concat(first_name,' ',last_name)) as full_name from employees limit 0,10;
+-------------+------------------+
| employee_id | full_name |
+-------------+------------------+
| 100 | steven k_ing |
| 101 | neena kochhar |
| 102 | lex de haan |
| 103 | alexander hunold |
| 104 | bruce ernst |
| 105 | david austin |
| 106 | valli pataballa |
| 107 | diana lorentz |
| 108 | nancy greenberg |
| 109 | daniel faviet |
+-------------+------------------+
10 rows in set (0.06 sec)
mysql>
concat
···sql
select employee_id,concat(first_name,’ ',last_name) as full_name from employees limit 0,10;
···
mysql> select employee_id,first_name,last_name from employees limit 0,10;
+-------------+------------+-----------+
| employee_id | first_name | last_name |
+-------------+------------+-----------+
| 100 | Steven | K_ing |
| 101 | Neena | Kochhar |
| 102 | Lex | De Haan |
| 103 | Alexander | Hunold |
| 104 | Bruce | Ernst |
| 105 | David | Austin |
| 106 | Valli | Pataballa |
| 107 | Diana | Lorentz |
| 108 | Nancy | Greenberg |
| 109 | Daniel | Faviet |
+-------------+------------+-----------+
10 rows in set (0.07 sec)
mysql> select employee_id,concat(first_name,' ',last_name) as full_name from employees limit 0,10;
+-------------+------------------+
| employee_id | full_name |
+-------------+------------------+
| 100 | Steven K_ing |
| 101 | Neena Kochhar |
| 102 | Lex De Haan |
| 103 | Alexander Hunold |
| 104 | Bruce Ernst |
| 105 | David Austin |
| 106 | Valli Pataballa |
| 107 | Diana Lorentz |
| 108 | Nancy Greenberg |
| 109 | Daniel Faviet |
+-------------+------------------+
10 rows in set (0.07 sec)
mysql>
length
mysql> select employee_id,length(concat(first_name,' ',last_name)) as full_name from employees limit 0,10;
+-------------+-----------+
| employee_id | full_name |
+-------------+-----------+
| 100 | 12 |
| 101 | 13 |
| 102 | 11 |
| 103 | 16 |
| 104 | 11 |
| 105 | 12 |
| 106 | 15 |
| 107 | 13 |
| 108 | 15 |
| 109 | 13 |
+-------------+-----------+
10 rows in set (0.06 sec)
mysql>
substr
mysql> select employee_id,substr(concat(first_name,' ',last_name),1,5) as full_name from employees limit 0,10;
+-------------+-----------+
| employee_id | full_name |
+-------------+-----------+
| 100 | Steve |
| 101 | Neena |
| 102 | Lex D |
| 103 | Alexa |
| 104 | Bruce |
| 105 | David |
| 106 | Valli |
| 107 | Diana |
| 108 | Nancy |
| 109 | Danie |
+-------------+-----------+
10 rows in set (0.06 sec)
mysql>
instr
mysql> select employee_id,instr(concat(first_name,' ',last_name),'x') as full_name from employees limit 0,10;
+-------------+-----------+
| employee_id | full_name |
+-------------+-----------+
| 100 | 0 |
| 101 | 0 |
| 102 | 3 |
| 103 | 4 |
| 104 | 0 |
| 105 | 0 |
| 106 | 0 |
| 107 | 0 |
| 108 | 0 |
| 109 | 0 |
+-------------+-----------+
10 rows in set (0.05 sec)
mysql>
lpad
对 str进行左边填补直至它的长度达到 len个字符长度,然后返回 str。如果 str的长度长于 len’,那么它将被截除到 len个字符。
mysql> select employee_id,lpad(phone_number,20,'*') from employees limit 0,10;
+-------------+---------------------------+
| employee_id | lpad(phone_number,20,'*') |
+-------------+---------------------------+
| 100 | ********515.123.4567 |
| 101 | ********515.123.4568 |
| 102 | ********515.123.4569 |
| 103 | ********590.423.4567 |
| 104 | ********590.423.4568 |
| 105 | ********590.423.4569 |
| 106 | ********590.423.4560 |
| 107 | ********590.423.5567 |
| 108 | ********515.124.4569 |
| 109 | ********515.124.4169 |
+-------------+---------------------------+
10 rows in set (0.06 sec)
mysql>