ORACLE CONCAT()

오라클 CONCAT()

 

-오라클에서 문자열을 이어붙일 수 있는 함수

 

CONCAT(char1, char2)

 

CONCAT returns char1 concatenated with char2. Both char1 and char2 can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB. The string returned is in the same character set as char1. Its data type depends on the data types of the arguments.

 

In concatenations of two different data types, Oracle Database returns the data type that results in a lossless conversion. Therefore, if one of the arguments is a LOB, then the returned value is a LOB. If one of the arguments is a national data type, then the returned value is a national data type. 

 

• CONCAT(CLOB, NCLOB) returns NCLOB
• CONCAT(NCLOB, NCHAR) returns NCLOB
• CONCAT(NCLOB, CHAR) returns NCLOB
• CONCAT(NCHAR, CLOB) returns NCLOB

This function is equivalent to the concatenation operator(||).

 

 

SQL > SELECT CONCAT('AAA', 'BBBB') FROM DUAL;

   --> AAABBBB

 

SQL > SELECT CONCAT('AAA', 'BBBB', 'CCCC') FROM DUAL;

   --> ORA-00909: invalid number of arguments

 

SQL > SELECT 'AAA' || 'BBBB' FROM DUAL ;

   --> AAABBBB

 

SQL > SELECT 'AAA' || 'BBBB' || 'CCCC' FROM DUAL ;

   --> AAABBBBCCCC

 

SQL > SELECT CONCAT('AAA', CONCAT('BBBB', 'CCCC')) FROM DUAL;

   --> AAABBBBCCCC

 

 

CONCAT 을 이용해서 여러개의 문자열을 한번에 이어붙일려면 || 을 사용하거나, CONCAT('AAA', CONCAT('BBBB', 'CCCC'))  와 같이 사용한다.