In the MySQL mode of OceanBase Database, available string concatenation functions include CONCAT() and CONCAT_WS(). "||" indicates the logical operator "OR" by default. If sql_mode is specified, "||" can be used as a string concatenation operator.
This topic describes how to use string concatenation functions in queries and provides examples.
Example 1: Query the customer name in the cust table
SQL statement in the MySQL mode of OceanBase Database:
/*Create a table and insert proper 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 affected
obclient> INSERT INTO cust VALUES(102,'Madeleine','Johnson',23.00);
1 row affected
obclient> INSERT INTO cust VALUES(103,'Michael','Brown',9.05);
1 row affected
obclient> SELECT CONCAT_WS(' ', c_first, c_last) full_name FROM cust ORDER BY c_last LIMIT 2;
+-------------------+
| full_name |
+-------------------+
| Michael Brown |
| Madeleine Johnson |
+-------------------+
2 rows in set
Example 2: Use "||" as a string concatenation operator
If you add PIPES_AS_CONCAT as a value of the sql_mode variable in MySQL mode, "||" is used as a string concatenation operator. Syntax:
obclient> SET SESSION sql_mode='PIPES_AS_CONCAT,STRICT_TRANS_TABLES,STRICT_ALL_TABLES';
Query OK, 0 rows affected
obclient> SELECT c_first || ' ' || c_last full_name FROM cust ORDER BY c_last LIMIT 2;
+-------------------+
| full_name |
+-------------------+
| Michael Brown |
| Madeleine Johnson |
+-------------------+
2 rows in set