Use string concatenation operators in queries

This topic provides specific examples on how to use string concatenation operators in queries.

String concatenation operators

In the MySQL compatible mode of OceanBase Cloud, 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.

Examples

Create a test table and insert proper data into the table.

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

Use string concatenation operators in a query

• Use "||" as a string concatenation operator in a query.

  If you add PIPES_AS_CONCAT as a value of the sql_mode variable, "||" is used as a string concatenation operator.

  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
  

• Use the CONCAT_WS() function to concatenate strings.

  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