英文:
Oracle: concatenate with NULL
问题
在Oracle中，'a'||NULL会返回a，这在其他数据库管理系统中与我的经验不同。
这意味着像coalesce('id: '||id,'missing')这样的表达式不会起作用，因为第一个表达式从不为NULL。
我想我可以使用CASE … END表达式来完成这项任务，但这可能有点过于复杂。
我该如何使连接返回NULL，或者是否有其他简单的技巧我应该使用？
英文:
It appears that in Oracle, 'a'||NULL give you a, which is not my experience in other DBMSs.
This means that an expression like coalesce('id: '||id,'missing') won’t work, since the first expression is never NULL.
I suppose I can use a CASE … END expression to get the job done, but that’s starting to go over the top.
How can I get the concatenation to return NULL, or is there some other simple trick I should be using?
答案1
得分: 1
Oracle对NULL的处理表现不一。一方面，有一些函数如GREATEST、LEAST等，如果单个操作数为NULL，将返回NULL。另一方面，诸如MAX、MIN、SUM、AVG之类的聚合函数在聚合过程中会方便地忽略NULL。类似地，字符串运算符将NULL视为空字符串。实际上，''对于字符串数据类型等效于NULL。我们经常依赖此特性来组装带有可选/条件动态生成附加项的连接字符串。你不会希望邮件正文为空，因为你拼接的消息体的某部分恰好为NULL。
关于coalesce('id: '||id,'missing')，有一些选项：
- 在SQL中适用的
DECODE(id,NULL,'missing','id: '||id)，在PL/SQL中不适用。 - 使用
CASE WHEN (id IS NULL) THEN 'missing' ELSE 'id: '||id END。 - 使用
NVL(NULLIF('id: '||id,'id: '),'missing')。
英文:
Oracle exhibits mixed behavior with NULLs. On the one hand, there are several functions like GREATEST, LEAST, etc.. that will return NULL if a single operand is NULL. On the other hand, aggregations like MAX, MIN, SUM, AVG will conveniently ignore NULLs during aggregation. Similarly, string operators treat NULL as an empty string. In fact, '' is equivalent to NULL for a string datatype. We regularly rely on this to assemble concatenated strings with optional/conditional, dynamically generated additions. You wouldn't want an email body to be blank because some piece of your message body that you spliced in happened to be NULL.
In terms of coalesce('id: '||id,'missing'), here are some options:
DECODE(id,NULL,'missing','id: '||id) -- works in SQL only, not in PL/SQL
CASE WHEN (id IS NULL) THEN 'missing' ELSE 'id: '||id END
NVL(NULLIF('id: '||id,'id: '),'missing')
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论