This topic describes how to use string functions in queries through specific examples.
String functions
Common string functions include the length functions LENGTH() and CHAR_LENGTH(), the substring function SUBSTR(), the concatenation functions CONCAT() and CONCAT_WS(), the case conversion functions UPPER() and LOWER(), and the prefix and suffix trimming functions LTRIM(), RTRIM(), and TRIM().
Examples
Create a sample table and insert appropriate data.
obclient> CREATE TABLE cust (
c_id INTEGER,
c_first VARCHAR(20),
c_last VARCHAR(20),
c_credit DECIMAL(10, 2)
);
Query OK, 0 rows affected
obclient> INSERT INTO cust VALUES(101,'Ann','Smith',16.10);
1 row in set
obclient> INSERT INTO cust VALUES(102,'Madeleine','Johnson',23.00);
1 row in set
obclient> INSERT INTO cust VALUES(103,'Michael','Brown',9.05);
1 row in set
Use string functions in queries
You can use the string functions CHAR_LENGTH(), CONCAT(), CONCAT_WS(), SUBSTR(), and UPPER() to query data.
obclient> SELECT CHAR_LENGTH(c_last) namelength
, CONCAT(c_first, c_last) full_name1
, CONCAT_WS('_', c_first, c_last) full_name2
, SUBSTR(c_last, 3)
, UPPER(c_last)
FROM cust ORDER BY c_last LIMIT 3;
+------------+------------------+-------------------+-------------------+---------------+
| namelength | full_name1 | full_name2 | SUBSTR(c_last, 3) | UPPER(c_last) |
+------------+------------------+-------------------+-------------------+---------------+
| 5 | MichaelBrown | Michael_Brown | own | BROWN |
| 7 | MadeleineJohnson | Madeleine_Johnson | hnson | JOHNSON |
| 5 | AnnSmith | Ann_Smith | ith | SMITH |
+------------+------------------+-------------------+-------------------+---------------+
3 rows in set
Note that the length unit of the LENGTH() function is bytes, while the length unit of the CHAR_LENGTH() function is characters. Example:
obclient> SELECT CHAR_LENGTH('中国'), LENGTH('中国'),CHAR_LENGTH('hello');
+-----------------------+------------------+----------------------+
| CHAR_LENGTH('中国') | LENGTH('中国') | CHAR_LENGTH('hello') |
+-----------------------+------------------+----------------------+
| 2 | 6 | 5 |
+-----------------------+------------------+----------------------+
1 row in set