NULLIF比使用完整的IFF或CASE语句要短一些
我有下面的fb.org_story，它返回一些空行，我想更改它，使其显示NULL(不创建文件格式(
select
fb.org_story as pvt_story
from prod.facebook fb
SELECT
NULLIF(fb.org_story,'') AS pvt_story
FROM prod.facebook AS fb
但如果字符串周围有空格，您可能也需要修剪它，因此我倾向于使用：
SELECT
NULLIF(TRIM(fb.org_story),'') AS pvt_story
FROM prod.facebook AS fb
因此，如果您有NULL以及它们显示为文本NULL的内容，我们需要将其转换为text，然后转换为COALESCE、NVL、IFNULL，即所需的输出
SELECT
fb.org_story AS pvt_story
,fb.org_story::text AS as_text
,NVL(fb.org_story::text, 'NULL') as null_says_null_a
,COALESCE(fb.org_story::text, 'NULL') as null_says_null_b
,IFNULL(fb.org_story::text, 'NULL') as null_says_null_c
,IFF(fb.org_story IS NULL, 'NULL', fb.org_story::text ) as null_says_null_d
FROM VALUES (123), (null) AS fb(org_story)
| PVT_STORY | AS_TEXT | NULL_SAYS_NULL_A | NULL_SAYS_NULL_B | NULL
|---|---|---|---|
| 123 | 223 | 122 | 123123 |
| 空 | 空
您也可以使用联合((和
select coalesce(fb.org_story,'') AS pvt_story
FROM prod.facebook AS fb
如果数据中有空格，请尝试其中一个：
-- If you want to return `NULL` values
SELECT NULLIF(TRIM(' '), '') + 1 AS "Test 1"; -- Returns NULL
SELECT NULLIF(TRIM(NULL), '') + 2 AS "Test 2"; -- Returns NULL
SELECT NULLIF(TRIM(''), '') + 3 AS "Test 3"; -- Returns NULL
-- If you want to return calculated values even if the field is null.
SELECT NVL(NULLIF(TRIM(' '), ''), 0) + 4 AS "Test 4"; -- Returns 4
SELECT NVL(NULLIF(TRIM(NULL), ''), 0) + 5 AS "Test 5"; -- Returns 5
SELECT NVL(NULLIF(TRIM(''), ''), 0) + 6 AS "Test 6"; -- Returns 6
示例用法：
SELECT NULLIF(TRIM(fb.org_story), '') + 7 AS "pvt_story"
FROM prod.facebook AS fb; -- Returns NULL
SELECT NVL(NULLIF(TRIM(fb.org_story), ''), 0) + 8 AS "pvt_story"
FROM prod.facebook AS fb; -- Returns 8