Oracle Database Concat and | |

1. Brief description:

Introduce this issue with several SQL statements
Create a new table and insert a few data, as follows:

CREATE TABLE tab1 (col1 VARCHAR2(6), col2 CHAR(6),        col3 VARCHAR2(6), col4 CHAR(6) );INSERT INTO tab1 (col1,  col2,     col3,     col4)        VALUES   (‘abc‘, ‘def   ‘, ‘ghi   ‘, ‘jkl‘);        INSERT INTO tab1 (col1,  col2,     col3,     col4)       VALUES   (‘1a‘, ‘456   ‘, ‘321   ‘, ‘234‘);       INSERT INTO tab1 (col1,  col2,     col3,     col4)       VALUES   (‘45345‘, ‘656   ‘, ‘65   ‘, ‘a1‘);

In the case of Fuzzy query based on the conditions, there are the following three ways:

SELECT * from tab1 t where t.col1 like ‘%a%‘;SELECT * from tab1 t where t.col1 like ‘%‘ || ‘a‘ || ‘%‘;SELECT * from tab1 t where t.col1 like  concat(concat(‘%‘,‘a‘),‘%‘);

As the equivalent of the three kinds of writing, the most commonly used is the first way of writing, the second third use splicing, but the difference is that || can be infinite splicing, similar to the concatenation of strings + , but concat is oracle in the function, CONCAT(char1 , char2) this function has only 2 parameters, So the third way of stitching 2 times.

2. Other uses

The following sql:

SELECT col1||col2||col3||col4 "Concatenation"  FROM tab1;

The query results are as follows:

SELECT ‘col1=‘ || t.col1 || ‘,col2=‘ || t.col2 "字段拼接"        FROM tab1 t;

The query results are as follows:

That is, when we need to customize the results of the query, use || stitching.

3. The cases I have used:

In java , you need to map set the put data in multiple tables inside the collection.

SELECT ‘map.put("‘ || t.col1 || ‘","‘ || t.col2 || ‘")‘ "put"        FROM tab1 t;

The results of this query are as follows:

This allows you to copy the results into your java code and use them.

Note: In oracle , need the above multiple stitching should be used || , because concat() only 2 stitching at a time, need to be nested multiple times. While in mysql , you can achieveconcat(col1,col2,coln....)
All the tables, inserts, and queries in this article are oracle databases.
Finally, you should refer to the official documentation for more information, as follows:
Concat
Concatenation Operator

Oracle Database Concat and | |