카테고리:: 조건식 함수

COALESCE¶

인자 중 첫 번째 NULL이 아닌 식을 반환하거나, 모든 인자가 NULL이면 NULL을 반환합니다.

구문¶

COALESCE( <expr1> , <expr2> [ , ... , <exprN> ] )

Copy

사용법 노트¶

Snowflake performs implicit conversion of arguments to make them compatible. For example, if one of the input expressions is a numeric type, the return type is also a numeric type. That is, SELECT COALESCE('17', 1); first converts the VARCHAR value '17' to the NUMBER value 17, and then returns the first non-NULL value.

When conversion isn’t possible, implicit conversion fails. For example, SELECT COALESCE('foo', 1); returns an error because the VARCHAR value 'foo' can’t be converted to a NUMBER value.

We recommend passing in arguments of the same type or explicitly converting arguments if needed.

When implicit conversion converts a non-numeric value to a numeric value, the result is a value of type NUMBER(18,5).

상수가 아닌 숫자형 문자열 인자의 경우 NUMBER(18,5)가 숫자 값을 나타내기에 충분하지 않으면 해당 값을 나타낼 수 있는 형식으로 인자를 형 변환 해야 합니다.

데이터 정렬 세부 정보¶

The collation specifications of all input arguments must be compatible.
The comparisons follow the collation based on the input arguments’ collations and precedences.
The collation of the result of the function is the highest-precedence collation of the inputs.

예¶

The following example shows the values in three columns and then the result when the COALESCE function is applied to the three columns:

SELECT column1,
       column2,
       column3,
       COALESCE(column1, column2, column3) AS coalesce_result
  FROM (values
    (1,    2,    3   ),
    (null, 2,    3   ),
    (null, null, 3   ),
    (null, null, null),
    (1,    null, 3   ),
    (1,    null, null),
    (1,    2,    null)
  ) v;

Copy

+---------+---------+---------+-----------------+
| COLUMN1 | COLUMN2 | COLUMN3 | COALESCE_RESULT |
|---------+---------+---------+-----------------|
|       1 |       2 |       3 |               1 |
|    NULL |       2 |       3 |               2 |
|    NULL |    NULL |       3 |               3 |
|    NULL |    NULL |    NULL |            NULL |
|       1 |    NULL |       3 |               1 |
|       1 |    NULL |    NULL |               1 |
|       1 |       2 |    NULL |               1 |
+---------+---------+---------+-----------------+