NULL 或空白字段 (ORACLE)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9165491/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
NULL or BLANK fields (ORACLE)
提问by user1192996
I'm new to Oracle, so my question might sound silly. I did go through the previous posts, but no luck. In the table, there is a column which is blank, and i am trying to find out the blank count in the column. I tried:
我是 Oracle 的新手，所以我的问题可能听起来很愚蠢。我确实浏览了以前的帖子，但没有运气。在表格中，有一列是空白的，我试图找出该列中的空白计数。我试过：
SELECT COUNT (COL_NAME) FROM TABLE WHERE COL_NAME IS NULL
SELECT COUNT (COL_NAME) FROM TABLE WHERE COL_NAME = ' '
SELECT COUNT (COL_NAME) FROM TABLE WHERE TRIM (COL_NAME)= ' '
The result to all the queries above is 0
上面所有查询的结果都是 0
However, when i did
然而，当我做
SELECT COL_NAME DUMP (COL_NAME,1016) FROM TABLE
gave me:
给我：
COL_NAME DUMP (COL_NAME,1016)
NULL
NULL
NULL
and so on..
等等..
But there are hundreds or thousands of blank fields/empty fields in that column. Can anyone please help me to find countof those blank/empty fields in that column ? I am using Toad for Oracle 9.0.1.8
但是该列中有成百上千个空白字段/空字段。谁能帮我找到计数该列中的空/空字段的？我使用 Toad for Oracle 9.0.1.8
回答by Shannon Severance
COUNT(expresion)returns the count of of rows where expresionis not null. So SELECT COUNT (COL_NAME) FROM TABLE WHERE COL_NAME IS NULLwill return 0, because you are only counting col_namewhere col_nameis null, and a count of nothing but nulls is zero. COUNT(*)will return the number of rows of the query:
COUNT(expresion)返回的行，其中的计数expresion是不为空。因此SELECT COUNT (COL_NAME) FROM TABLE WHERE COL_NAME IS NULL将返回 0，因为您只计算col_namewherecol_name为 null，而除了 null 之外什么都没有计数为零。COUNT(*)将返回查询的行数：
SELECT COUNT (*) FROM TABLE WHERE COL_NAME IS NULL
The other two queries are probably not returning any rows, since they are trying to match against strings with one blank character, and your dump query indicates that the column is actually holding nulls.
其他两个查询可能没有返回任何行，因为它们试图与一个空白字符的字符串进行匹配，并且您的转储查询表明该列实际上包含空值。
If you have rows with variable strings of space characters that you want included in the count, use:
如果您有想要包含在计数中的带有可变空格字符字符串的行，请使用：
SELECT COUNT (*) FROM TABLE WHERE trim(COL_NAME) IS NULL
trim(COL_NAME)will remove beginning and ending spaces. If the string is nothing but spaces, then the string becomes ''(empty string), which is equivalent to null in Oracle.
trim(COL_NAME)将删除开头和结尾的空格。如果字符串只是空格，则字符串变为''（空字符串），这相当于 Oracle 中的 null。
回答by Devon_C_Miller
A NULL column is not countable, however a row that has a NULL column is. So, this should do what you're looking for:
NULL 列不可数，但具有 NULL 列的行是可数的。所以，这应该做你正在寻找的：
SELECT COUNT (*) FROM TABLE WHERE COL_NAME IS NULL OR LENGTH(TRIM (COL_NAME)) = 0
Note, that there are non-printing characters that this will not address. For example U+00A0 is the non-breaking space character and a line containing that will visually appear empty, but will not be found by the tests above.
请注意，存在这不会解决的非打印字符。例如，U+00A0 是不间断的空格字符，包含的行在视觉上看起来是空的，但不会被上面的测试找到。
回答by Roger Lindsj?
You can not count nulls (at least not in Oracle). Instead try this
你不能计算空值（至少在 Oracle 中不是）。而是试试这个
SELECT count(1) FROM TABLE WHERE COL_NAME IS NULL
回答by ebbie73
So I just wondered about the same, but had at the same time had a solution to this. I wanted a query that included all rows in the table and counting both blanks and non blanks. So I came up with this.
所以我只是想知道同样的问题，但同时有一个解决方案。我想要一个包含表中所有行并计算空白和非空白的查询。所以我想出了这个。
SELECT COUNT(col_name) VALUE_COUNT
COUNT(NVL(col_name, 'X') - COUNT(col_name) NULL_VALUE_COUNT
FROM table
[CONDITIONS]
Instead of the NVL function you can count the primary key column to obtain the total count of rows
代替 NVL 函数，您可以计算主键列以获得总行数
It works like a charm
它就像一个魅力
回答by George Janga
SELECT COUNT (COL_NAME)
FROM TABLE
WHERE TRIM (COL_NAME) IS NULL
or COL_NAME='NULL'
回答by EvilTeach
DROP TABLE TEST; -- COMMENT THIS OUT FOR THE FIRST RUN
CREATE TABLE TEST
(
COL_NAME,
TEST_NAME
) AS
(
SELECT NULL, 'ACTUAL NULL' FROM DUAL
UNION ALL
SELECT '', 'NULL STRING' FROM DUAL
UNION ALL
SELECT ' ', 'SINGLE SPACE' FROM DUAL
UNION ALL
SELECT ' ', 'DOUBLE SPACE' FROM DUAL
UNION ALL
SELECT ' ', 'TEN SPACES' FROM DUAL
UNION ALL
SELECT 'NONSPACE', 'NONSPACES' FROM DUAL
)
;
SELECT LENGTH(COL_NAME) NUM_OF_SPACES, TEST_NAME
FROM TEST
WHERE LENGTH(COL_NAME) > 0 -- THERE IS SOMETHING IN THE FIELD
AND TRIM(COL_NAME) IS NULL; -- WHICH EQUATES TO NULL
table TEST dropped.
table TEST created.
NUM_OF_SPACES TEST_NAME1 SINGLE SPACE 2 DOUBLE SPACE 10 TEN SPACES
表 TEST 已删除。
表 TEST 创建。
NUM_OF_SPACES TEST_NAME1 SINGLE SPACE 2 DOUBLE SPACE 10 TEN SPACES
Once you have identified the columns that contain blanks, wrap that query in a count. If you actually need to identify the fields for some kind of update, consider selecting the ROWID as well.
确定包含空格的列后，将该查询包装在计数中。如果您确实需要为某种更新确定字段，也可以考虑选择 ROWID。
回答by paulsm4
One should NEVER treat "BLANK" and NULL as the same.
永远不要将“BLANK”和NULL视为相同。
Back in the olden days before there was a SQL standard, Oracle made the design decision that empty strings in VARCHAR/ VARCHAR2 columns were NULL and that there was only one sense of NULL (there are relational theorists that would differentiate between data that has never been prompted for, data where the answer exists but is not known by the user, data where there is no answer, etc. all of which constitute some sense of NULL). By the time that the SQL standard came around and agreed that NULL and the empty string were distinct entities, there were already Oracle users that had code that assumed the two were equivalent. So Oracle was basically left with the options of breaking existing code, violating the SQL standard, or introducing some sort of initialization parameter that would change the functionality of potentially large number of queries. Violating the SQL standard (IMHO) was the least disruptive of these three options.
早在 SQL 标准出现之前，Oracle 就做出了设计决策，即 VARCHAR/VARCHAR2 列中的空字符串为 NULL，并且只有一种 NULL 含义（有关系理论家会区分从未出现过的数据）提示，答案存在但用户不知道的数据，没有答案的数据等，所有这些都构成某种意义上的NULL）。当 SQL 标准出现并同意 NULL 和空字符串是不同的实体时，已经有 Oracle 用户的代码假定这两者是等效的。因此，Oracle 基本上只能选择破坏现有代码、违反 SQL 标准或引入某种初始化参数来改变潜在的大量查询的功能。
Oracle has left open the possibility that the VARCHAR data type would change in a future release to adhere to the SQL standard (which is why everyone uses VARCHAR2 in Oracle since that data type's behavior is guaranteed to remain the same going forward).
Oracle 保留了 VARCHAR 数据类型在未来版本中更改以符合 SQL 标准的可能性（这就是每个人在 Oracle 中使用 VARCHAR2 的原因，因为该数据类型的行为保证在未来保持不变）。
回答by Brian
Try the nvl function. select count(nvl(col_name,0)) from table.
试试 nvl 功能。从表中选择 count(nvl(col_name,0))。
Sorry I reread the OP. What is the table structure? is the column varchar or char as that will make a difference?
抱歉，我重读了 OP。什么是表结构？列 varchar 还是 char 会有所不同？
try
尝试
select count(col_name), distinct(col_name) from table group by distinct(col_name)
/can't remember if you need distinct in the group by but I think not/
/不记得你是否需要在组中与众不同，但我认为不需要/
and see if it gives you a return with a column name that is blank.
看看它是否给你一个列名空白的回报。
回答by paulsm4
First, you know that "blank" and "null" are two COMPLETELY DIFFERENT THINGS? Correct?
首先，您知道“空白”和“空”是两种完全不同的东西吗？正确的？
Second: in most programming languages, "" means an "empty string". A zero-length string. No characters in it.
第二：在大多数编程语言中，“”表示“空字符串”。零长度字符串。里面没有字符。
SQL doesn't necessarily work like that. If I define a column "name char(5)", then a "blank" name will be " "(5 spaces).
SQL 不一定像那样工作。如果我定义一列“name char(5)”，那么“空白”名称将为" "（5 个空格）。
It sounds like you might want something like this:
听起来你可能想要这样的东西：
select count(*) from my_table where Length(trim(my_column)) = 0;
"Trim()" is one of many Oracle functions you can use in PL/SQL. It's documented here:
“Trim()”是您可以在 PL/SQL 中使用的众多 Oracle 函数之一。它记录在这里：
http://www.techonthenet.com/oracle/functions/trim.php
http://www.techonthenet.com/oracle/functions/trim.php
'Hope that helps!
'希望有帮助！
