SQL Select one statement expect values start with

Question

I have the following code here:

SELECT productcode, Brand, product, size
from outerbarcodes i
where productcode = '' or productcode is null or
     brand = '' or brand is null or
     product = '' or product is null or
     size = '' or size is null;

Id like to add a condition not to include values that start with 'PK' inside column productcode. How would the code be amended?

Thanks in advance

@vc74 MySQL doesn't work that way. – Tim Biegeleisen Mar 04 '19 at 11:19 — Tim Biegeleisen, Mar 04 '19 at 11:19

Fahmi · Answer 1 · 2019-03-04T11:28:34.127

1

use not like operator

`SELECT productcode, Brand, product, size
 from outerbarcodes i
 where ((productcode = '' or productcode is null) and productcode not like 'PK%') or
      (brand = '' or brand is null) or
      (product = '' or product is null) or
      (size = '' or size is null);`

edited Mar 04 '19 at 11:28

answered Mar 04 '19 at 11:15

Fahmi

37,315
5
22
31

1

You don't need all those parentheses :-) – Tim Biegeleisen Mar 04 '19 at 11:32

score 1 · Answer 2 · answered Mar 04 '19 at 11:17

1

You may just add a LIKE condition to your current where clause:

SELECT productcode, Brand, product, size
FROM outerbarcodes i
WHERE
    (productcode > '' AND productcode NOT LIKE 'PK%') OR
    brand > '' OR
    product > '' OR
    size > '';

answered Mar 04 '19 at 11:17

Tim Biegeleisen

502,043
27
286
360

I have just tested it and it does makes sense using the operator not like 'pk%'. It still returns the values starting with 'PK'. Im gonna have a read on this https://stackoverflow.com/questions/40376260/not-like-and-like-not-returning-opposite-result something to do with null values... – Mar 04 '19 at 11:29
@Eduards You should add sample data to your question. My logic looks correct to me, at least based on what my interpretation of your question is. – Tim Biegeleisen Mar 04 '19 at 11:33
I totally agree with you Tim, I believe its something to do with null values that causes this issue. I have posted an answer with the code I have just used. And it works perfectly fine. Ill just have to amend it to not show null values – Mar 04 '19 at 11:34

score 0 · Accepted Answer · answered Mar 04 '19 at 11:39

0

I think you want:

select productcode, Brand, product, size
from outerbarcodes i
where (productcode = '' or productcode is null or
       brand = '' or brand is null or
       product = '' or product is null or
       size = '' or size is null
      ) and
      productcode not like 'PK%';

I'm a little confused by the other answers, because they are changing the comparisons for the other columns -- instead of checking for empty columns, they are checking for columns that have values.

answered Mar 04 '19 at 11:39

Gordon Linoff

1,242,037
58
646
786

Sorry, thats the correct code you have just given. Sorry didnt realise what was going on. Well spotted on other answers – Mar 04 '19 at 11:44
what does the 'i' represent after `from outerbarcodes i` – Mar 04 '19 at 11:46
@Eduards . . . It is a table alias. You can qualify the column names by using it, for example, `i.productcode`. Because the query has only one table reference, qualifying the column names is more optional. – Gordon Linoff Mar 04 '19 at 11:51
Thats going to be very useful for me to know, thank you! – Mar 04 '19 at 11:56

SQL Select one statement expect values start with

3 Answers3