CODEWITHSUNDEEP
sqloracle-database
D0uble0
D0uble0

Reputation: 175

SQL query syntax when dealing with multi-valued fields

I am constructing a SQL Developer query to retrieve records with a specified status'. Multiple values can also be selected. How can I update my query to include records with multiple "Status" values? which are semicolon delimited i.e. Off; Pending

Here is my query which only returns records with a singular status:

Select * from LOGS where Status= 'Off' or Status = 'Pending' or Status = 'ON';

Upvotes: 0

Views: 522

Answers (2)

Steve Lovell
Steve Lovell

Reputation: 2564

How about:

Select * from LOGS
where
    concat('; ', Status,'; ') like '%; Off; %' or
    concat('; ', Status,'; ') like '%; Pending; %' or 
    concat('; ', Status,'; ') like '%; On; %'    ;

or perhaps better

Select * from LOGS
where
    REGEXP_LIKE (concat('; ', Status,'; '),'; Off; |; Pending; |; On; ')

Upvotes: 0

Gabri T
Gabri T

Reputation: 454

Select * from LOGS where Status= 'Off' and Status = 'Pending' and Status = 'ON';

you must mean OR´s instead of AND´s, the same column cannot have those 3 values on the same row

   Select * from LOGS where Status= 'Off' or Status = 'Pending' or Status = 'ON';

this change will allow you to get every LOGS row where any of those 3 status is valid

if you mean that status can have multiple values like "Off; Pending;ON" all you have to do is:

 Select * from LOGS where Status like '%Off%' or Status like '%Pending%' or Status like '%ON%';

this means "bring every status that have Off on it, even if it is Offline, 123Off123" and so on to the other cases, so be carefull on your status

Upvotes: 1

Related Questions