用字符串替换任何 NULL 字段 [英] Replacing any NULL fields with a string

问题描述

我是 SQL 新手，这个问题难倒了我.我应该编写一个 SQL 语句来列出表中的所有数据，但是如果一列可能包含 NULL 值(在这种情况下只有一个字段是 NULL)，则必须在该列中打印短语-NONE-"排.实验室提示我必须使用一个或多个单行函数来完成此操作.

I'm new to SQL and this problem has stumped me. I am supposed to write an SQL statement that lists all the data in a table, but if a column might contain NULL values (in this case only one field is NULL), the phrase "-NONE-" must print in that column for that row. The lab hints that I must use one or more single-row functions to accomplish this.

我们正在使用 Oracle SQL Developer 来测试脚本.我正在尝试使用 REPLACE 函数，但我不断收到错误消息.我曾尝试使用 NVAL、REPLACE 和其他类似的函数，但显然我的语法是错误的.例如:

We are using Oracle SQL Developer to test scripts. I am trying to use the REPLACE function but I keep getting errors. I've tried using NVAL, REPLACE, and other similar functions but I am clearly getting the syntax wrong. For example:

SELECT *, REPLACE(manager_id, NULL, '-NONE-')
FROM departments;

^总是返回错误:在预期的地方缺少 FROM 语句

^Always returns an error: missing FROM statement where expected

推荐答案

你想要的函数是coalesce().这是ANSI标准函数.

The function you want is coalesce(). This is the ANSI standard function.

假设你的变量是一个字符串:

Assuming your variable is a character string:

SELECT d.*, COALESCE(manager_id, '-NONE-')
FROM departments d;

如果不是字符串，则先转换:

If it is not a string, then convert it first:

SELECT d.*, COALESCE(cast(manager_id as varchar(255)), '-NONE-')
FROM departments d;

这篇关于用字符串替换任何 NULL 字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！