如何构造sql查询以检查列的值是否为所有空格?

I have only spaces as values of rows in a column of length 121 chars.

在长度为121的列中，只有空格作为行的值。

I want to write a query in oracle to check whether the column string is only spaces.

我想在oracle中编写一个查询，以检查列字符串是否只是空格。

for e.g. if say for column address. a row contains only spaces(all 121 chars are spaces).

例如:if say for column address。一行只包含空格(所有121个字符都是空格)。

i want a query that will check if the row contains only spaces.

我想要一个查询，它将检查该行是否只包含空格。

select * from table where address <> ' ';

从地址<> '的表中选择*;

but this isnt working it only checks for 1 space. i want the query to check for all 121 spaces.

但这并不是工作它只检查1个空间。我要查询所有121个空格。

3 个解决方案

#1

You could just run a TRIM on the column. Depending on your DBMS, the Null string is treated as either a NULL or as an empty string ''. But for Oracle where the empty string is treated as a NULL, you should be able to do:

你可以在柱上做个修剪。根据您的DBMS，空字符串被视为空字符串或空字符串。但是对于空字符串被视为NULL的Oracle，您应该可以这样做:

select * from table where LTRIM(RTRIM(address)) IS NOT NULL

从LTRIM(RTRIM(地址))不为空的表中选择*。

Note that TRIM removes all whitespace, not just the space character.

注意，TRIM删除了所有空格，而不仅仅是空格字符。

#2

If you're checking for 121 spaces as a learning exercise then that's OK, but if you're storing 121 spaces in a column for a production application you may have a bad design.

如果你正在检查121个空格作为学习练习，那没关系，但是如果你在一个列中存储了121个空格，那么你可能会有一个糟糕的设计。

If the column type is CHAR it should probably be changed to VARCHAR (or VARCHAR2 in Oracle). If it already is VARCHAR/VARCHAR2 you should store an empty string as, well, an empty string.

如果列类型是CHAR，则应该将其改为VARCHAR(或Oracle中的VARCHAR2)。如果它已经是VARCHAR/VARCHAR2，那么您应该存储一个空字符串作为空字符串。

If you need the value padded to 121 spaces, take care of that when you query it:

如果您需要在121个空格中添加值，请在查询时注意:

Oracle: SELECT RPAD(NVL(address, ' '), 121) FROM myTable
Oracle:从myTable中选择RPAD(NVL(地址，')，121)。
MySQL: SELECT RPAD(address, 121, ' ') FROM myTable
MySQL:从myTable中选择RPAD(地址，121)。

If you want to find out if the value is empty, remember that Oracle treats an empty string as NULL. MySQL treats it as an empty string:

如果您想知道该值是否为空，请记住，Oracle将空字符串视为空。MySQL将其视为空字符串:

Oracle: SELECT * FROM myTable WHERE address IS NULL
Oracle:在地址为空的myTable中选择*。
MySQL: SELECT * FROM myTable WHERE address = ''
MySQL:从myTable中选择地址= "

#3

use REPLACE() function and compare it's result to null

使用REPLACE()函数并将其结果与null进行比较。

SELECT * FROM table WHERE REPLACE(address,' ') IS NULL;

Fiddle

小提琴

#1