MySQL选择哪个列不是空的。

In MySQL, can I select columns only where something exists?

在MySQL中，我可以只在存在的地方选择列吗?

For example, I have the following query:

例如，我有以下查询:

select phone, phone2
from jewishyellow.users
where phone like '813%'
and phone2

I'm trying to select only the rows where phone starts with 813 and phone2 has something in it.

我试着只选择以813开头的行，而phone2有一些内容。

10 个解决方案

#1

215

Compare value of phone2 with empty string:

用空字符串比较phone2的值:

select phone, phone2 
from jewishyellow.users 
where phone like '813%' and phone2<>''

Note that NULL value is interpreted as false.

注意，NULL值被解释为false。

#2

To check if field is NULL use IS NULL, IS NOT NULL operators.

要检查字段是否为NULL，使用的是NULL操作符，而不是NULL操作符。

MySql reference http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html

MySql参考http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html

#3

Check for NULL and empty string values:

检查空字符串值和空字符串值:

select phone
, phone2 
from users 
where phone like '813%' 
and trim(coalesce(phone2, '')) <>''

N.B. I think COALESCE() is SQL standard(-ish), whereas ISNULL() is not.

注意，我认为COALESCE()是SQL standard(-ish)，而ISNULL()不是。

#4

If there are spaces in the phone2 field from inadvertant data entry, you can ignore those records with the IFNULL and TRIM functions:

如果在phone2字段中有来自无意数据条目的空格，您可以使用IFNULL和TRIM函数忽略这些记录:

SELECT phone, phone2
FROM jewishyellow.users
WHERE phone LIKE '813%'
    AND TRIM(IFNULL(phone2,'')) <> '';

#5

select phone, phone2 from jewishyellow.users 
where phone like '813%' and phone2 is not null

#6

An answer that I've been using that has been working for me quite well that I didn't already see here (this question is very old, so it may have not worked then) is actually

我一直在使用的一个答案对我来说很有用，但我在这里没有看到(这个问题很老，所以当时可能还没用)

SELECT t.phone, 
       t.phone2 
  FROM jewishyellow.users t
 WHERE t.phone LIKE '813%' 
   AND t.phone2 > ''

Notice the > '' part, which will check if the value is not null, and if the value isn't just whitespace or blank.

请注意>“部分，它将检查该值是否为null，以及该值是否不仅仅是空格或空格。

Basically, if the field has something in it other than whitespace or NULL, it is true. It's also super short, so it's easy to write, and another plus over the COALESCE() and IFNULL() functions is that this is index friendly, since you're not comparing the output of a function on a field to anything.

基本上，如果字段中除了空格或NULL之外还有其他东西，这是正确的。它也非常短，所以写起来很容易，另一个加上COALESCE()和IFNULL()函数，这是一个索引友好的函数，因为您没有将一个字段上的函数的输出与任何东西进行比较。

Test cases:

测试用例:

SELECT if(NULL > '','true','false');-- false
SELECT if('' > '','true','false');-- false
SELECT if(' ' > '','true','false');-- false
SELECT if('\n' > '','true','false');-- false
SELECT if('\t' > '','true','false');-- false
SELECT if('Yeet' > '','true','false');-- true

#7

SELECT phone, phone2 
FROM jewishyellow.users 
WHERE phone like '813%' and (phone2 <> "");

May need some tweakage depending on what your default value is. If you allowed Null fill, then you can do "Not NULL" instead, which is obviously better.

可能需要根据默认值进行一些调整。如果你允许零填充，那么你可以用"Not Null "代替，这显然更好。

#8

We can use CASE for setting blank value to some char or String. I am using NA as Default string.

我们可以用大小写来设置某个char或String的空值。我使用NA作为默认字符串。

SELECT phone,   
CASE WHEN phone2 = '' THEN 'NA' END AS phone2 ELSE ISNULL(phone2,0) 
FROM jewishyellow.users  WHERE phone LIKE '813%'

#9

Use:

使用:

SELECT t.phone, 
       t.phone2 
  FROM jewishyellow.users t
 WHERE t.phone LIKE '813%' 
   AND t.phone2 IS NOT NULL

#10

you can use like operator wildcard to achieve this:

您可以使用like操作符通配符来实现以下目标:

SELECT t.phone, 
       t.phone2 
FROM jewishyellow.users t
WHERE t.phone LIKE '813%' 
  AND t.phone2 like '[0-9]';

in this way, you could get all phone2 that have a number prefix.

这样，您就可以得到所有有数字前缀的phone2。

#1

215