SQL query syntax when dealing with multi-valued fields

Question

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';

What do you mean - do you have a column STATUS and the values can be 'Off' or 'Pending' but they can also be 'Off;Pending'? (Or, with a space after the semicolon?) If so, why can't you add or Status = 'Off; Pending'? So perhaps that is not what you mean? Please clarify. — user5683823
– user5683823, Commented Jun 2, 2017 at 20:12

Gabri T · Accepted Answer · 2017-06-02 20:14:56Z

1

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

edited Jun 2, 2017 at 20:14

answered Jun 2, 2017 at 20:08

Gabri T

4542 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

D0uble0 Over a year ago

how do I now handle a case of multiple selected values?

Gabri T Over a year ago

is your question answered? i´m confused. can you be more specific?

Steve Lovell · Accepted Answer · 2017-06-02 20:29:48Z

0

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; ')

edited Jun 2, 2017 at 20:29

answered Jun 2, 2017 at 20:12

Steve Lovell

2,5742 gold badges15 silver badges18 bronze badges

5 Comments

Gabri T Over a year ago

'%Pending%' will not accept 'End', its case sensitive

Steve Lovell Over a year ago

Will that not depend on collation? I'm not so familiar with Oracle (thinking of SQL-Server)

Gabri T Over a year ago

i was answering for the sql case, i can´t test it now but i´m convinced it will not accept. if it does, i´m glad to hear. your solution with REGEX seems good but complex.

Gabri T Over a year ago

found something else, in your first answer you have: "; Off; ", it means that space characters are obligatory aswell, what if it is the last STATUS? will it have character ";" and " "?

Steve Lovell Over a year ago

@GabriT. I added semi-colons and spaces in my concat, and according to the OP, there is a space in when the field contains multiple items. It can be changed if necessary.

Collectives™ on Stack Overflow

SQL query syntax when dealing with multi-valued fields

2 Answers 2

2 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related