CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platformsnowflake-schema
lalaland
lalaland

Reputation: 341

How to grab records that have NULL or certain character

I have TWO column that contains NULL values and character "//" and valid values on both. I want to select all the rows that have NULL OR the character "//" in the first column and valid character in the second column. Is there a way to do this? I keep getting an error when I run this:

Does not work:

select COLUMN1 IS NULL OR LIKE '%//%' AND COLUMN2 NOT LIKE '%//%' 
    from TABLE

Works:

select COLUMN1 LIKE '%//N%' AND COLUMN2 NOT LIKE '%//%' 
from TABLE

How can I make the first statement work? To check for columns that have "//" OR has NULL values in the first column?

Upvotes: 0

Views: 126

Answers (3)

Himanshu Kandpal
Himanshu Kandpal

Reputation: 1606

try this

create or replace table test_1 as
(SELECT '1' as fld1, NULL as fld2 UNION ALL
SELECT '//' as fld1, NULL as fld2 UNION ALL
SELECT '21' as fld1, '5444' as fld2 UNION ALL
SELECT '//' as fld1 , '25' as fld2 UNION ALL
SELECT NULL as fld1, '//' as fld2  UNION ALL
SELECT '4500' as fld1 , '23' as fld2 UNION ALL
SELECT NULL as fld1 , '12' as fld2 );

--I want to select all the rows that have NULL OR the character "//" in the first column 
--and valid character in the second column.
select * from test_1 WHERE  (fld1 is NULL  and  (fld2 <> '//' and  fld2 is not null ))
OR (fld1 = '//'  and  (fld2 <> '//' and  fld2 is not null ));

Upvotes: 0

Greg Pavlik
Greg Pavlik

Reputation: 11046

Based on your description, you need to use parenthesis around your junction and disjunction (or, and) operators:

create or replace temp table foo (COLUMN1 string, COLUMN2 string);

insert into foo (COLUMN1, COLUMN2) values ('//',null), ('okay', null), ('okay', 'okay'), (null, 'okay'), ('//', 'okay');

select * from foo where 
(COLUMN1 = '//' or COLUMN1 is null) and (COLUMN2 <> '//' and COLUMN2 is not null)

As I understand the requirement, this will only select the final two rows that have // or NULL for the first column and valid values for the second column.

Upvotes: 1

rikyeah
rikyeah

Reputation: 2013

You can try to use UNION to achieve the same result.

select COLUMN1 LIKE '%//N%' AND COLUMN2 NOT LIKE '%//%' 
from TABLE
union
select COLUMN1 IS NULL AND COLUMN2 NOT LIKE '%//%' 
from TABLE

Upvotes: 0

Related Questions