我有一个连接 2 个表的查询，并显示名称如下：
select a.first_name||','||a.last_name as name, a.number as id, b.address
from student a, address b
where a.number = b.id;
在某些情况下，名称在学生表中没有值。输出看起来像：
Name Id Address
abc, def 100 CA
,yyy 101 MA
, 102 PA
所以我试着使用解码，
select decode(a.first_name||','||a.last_name,',','') as name, a.number as id, b.address
from student a, address b
where a.number = b.id;
但是这对于所有行中的名称字段返回 null。当它不存在时,我必须为名称显示 null 值。我需要摆脱逗号
1
NVL2()函数，它为第一个参数的not null或null情况带来结果，非常适合您的情况，在这种情况下，每当first_name通过函数的第二个参数（s.first_name||','）在从该列的值修剪空格后不为 null
SELECT NVL2(TRIM(s.first_name),s.first_name||',','')||s.last_name AS name,
s."number" AS id, a.address
FROM student s
JOIN address a
ON s."number" = a.id
如果您怀疑可能有一些不可打印的字符，则添加一个正则表达式，例如
SELECT NVL2(TRIM(REGEXP_REPLACE(s.first_name,'[^[:print:]]')),s.first_name||',','')||s.last_name AS name,
s."number" AS id, a.address
FROM student s
JOIN address a
ON s."number" = a.id
0
关于 NULLIF 函数-
select NULLIF(a.first_name||','||a.last_name, ',') as name, a.number as id, b.address
from student a, address b
where a.number = b.id;
0
为什么不使用 TRIM：
WITH demo_data AS ( SELECT 'abc' AS first_name, 'def' AS last_name, 100 AS id, 'CA' AS address FROM DUAL UNION ALL
SELECT '' AS first_name, 'yyy' AS last_name, 101 AS id, 'MA' AS address FROM DUAL UNION ALL
SELECT NULL AS first_name, NULL AS last_name, 102 AS id, 'PA' AS address FROM DUAL UNION ALL
SELECT 'ddd' AS first_name, NULL AS last_name, 103 AS id, 'CA' AS address FROM DUAL UNION ALL
SELECT NULL AS first_name, 'eee' AS last_name, 104 AS id, 'MA' AS address FROM DUAL )
SELECT TRIM(',' FROM first_name||','||last_name) as name,
id,
address
FROM demo_data;
结果：
NAME ID AD
------- ---------- --
abc,def 100 CA
,yyy 101 MA
102 PA
ddd 103 CA
eee 104 MA
本站系公益性非盈利分享网址，本文来自用户投稿，不代表码文网立场，如若转载，请注明出处
评论列表（18条）