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 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.

Examples

Create a 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 queries.

  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