SELECT statement where value not from enum

Question

I have a varchar column, called weather, I would like to validate its values.

The allowed values are from enum. As opposed to a regular enum column- where each time one of the enum values is allowd, this column allows several enum members, separated with a comma.

This is how I validate a simple enum column:

select *
from TableName
where weather not in ('SUN', 'RAIN', 'SNOW');

The weather contains SUN or RAIN or SNOW.

What if the weather contains SNOW,RAIN or RAIN,SNOW?
how can I validate there are only values from enum then?

Any ides? thoughts?

@almasshaikh, This is the requirement, I cannot change it. Any ideas? — user3165438, Dec 28 '14 at 08:51
Can you not add constraint like 'ADD CONSTRAINT CHK1 CHECK (weather IN ('SUN', 'RAIN', 'SNOW'))' — SMA, Dec 28 '14 at 09:09
@almasshaikh, Misundersand. where should I add this constraint? to the DB definition? If yes, I cannot. I only can check values, not more. — user3165438, Dec 28 '14 at 09:25
`NORMALIZE` your data first. You should not store multiple `values` in a single `column`. — Lalit Kumar B, Dec 28 '14 at 09:51

score 0 · Answer 1 · answered Dec 28 '14 at 10:04

0

Like operator is slow, but maybe on your data can works fast. Try it.

select *
from TableName
where (weather not like '%SUN%') and (weather not like '%RAIN%') and (weather not '%SNOW%');

answered Dec 28 '14 at 10:04

Maxim Korobov

2,574
1
26
44

Maheswaran Ravisankar · Answer 2 · 2014-12-28T10:37:07.343

This kind of requirement is not RDBMS friendly and against Codd's 12 Rules !!

But still possible.

Simplest elegant solution for you could be a PL/SQL function. It uses an associative array with hold your enum values as key.

CREATE OR REPLACE FUNCTION WEATHER_LOOKUP(input_string IN VARCHAR2)
RETURN NUMBER
IS
/* Associative array, can be used like a Map */
TYPE weather_tab IS TABLE OF VARCHAR2(20) INDEX BY VARCHAR2(4000);
weathers weather_tab;

temp_weather VARCHAR2(100);

flag NUMBER(1);
BEGIN
   /* Create a Map with your enum values as Key */
    weathers('SNOW') :=1;
    weathers('RAIN') :=2;
    weathers('SUN') :=3;

    /* If it is comma separated, split the tokens.. and iterate */
    FOR I IN 1..REGEXP_COUNT(input_string,',')+1 LOOP
     temp_weather := REGEXP_SUBSTR(input_string,'[^,]+',1,I);
     IF(weathers.EXISTS(temp_weather)) THEN
        flag := 1;
     ELSE
        flag := 0;
        EXIT;
     END IF;  
    END LOOP;

    RETURN flag;

END WEATHER_LOOKUP;
/

And your SQL is

SELECT * FROM MY_TABLE
where WEATHER_LOOKUP(weather) = 1;

Could you please be more specific about which of Codd's Rules it violates? I am in total agreement with you that data in need of tokenization is cumbersome to work with in Oracle DB. — user2672165, Dec 28 '14 at 14:39
It violates the rule of storing more than one piece of data in a field. — David Faber, Dec 29 '14 at 15:29

score 0 · Answer 3 · answered Dec 28 '14 at 10:40

The allowed values are from enum. As opposed to a regular enum column- where each time one of the enum values is allowd, this column allows several enum members, separated with a comma.

This is not an enum, but rather a set. Some RDBMS has direct support for that (like MySQL). But not Oracle (AFAIK). With Oracle the proper way of supporting that would be by using a nested table.

That being said, as if I understand it well, you cannot change your db schema. So, I would go for regular expression in order to solve such case:

...
where not regexp_like(weather,'^(((SUN)|(RAIN)|(SNOW))[,$])*');

^ from start of string
( ... )* zero of more occurrence of
- ((SUN)|(RAIN)|(SNOW)) SUN or RAINor SNOW
- [,$] followed by a coma or the end of string

This will not check for duplicate values though. Was that a requirement?

I tried this earlier.. but OP needs an `AND` and not `OR` ?? Means check all values(comma seprated) are from list.. — Maheswaran Ravisankar, Dec 28 '14 at 10:45
@Maheswaranv I didn't have time to look at this right now -- but as a quick answer to your comment: `not (A or B)` is `(not A) AND (not B)`. OP should definitively take a look at [De Morgan's law](http://en.wikipedia.org/wiki/De_Morgan%27s_laws). *This* will probably help her to solve this issue... — Sylvain Leroux, Dec 29 '14 at 14:39

score 0 · Answer 4 · answered Dec 28 '14 at 13:32

This seems to do the trick:

… WHERE NOT REGEXP_LIKE(weather, '^((SUN)|(RAIN)|(SNOW))(,((SUN)|(RAIN)|(SNOW)))*$')

See SQL Fiddle

Please:

note, this will allow duplicates, as long as they are valid on their own.
comment if and as further detail/adjustment is required.

score 0 · Answer 5 · edited May 23 '17 at 12:28

You can accomplish this using REGEXP_SUBSTR() and CONNECT BY (in Oracle at least - I can't speak for DB2):

WITH t1 AS (
    SELECT 1 AS id, 'SUN,RAIN,SNOW' AS weather
      FROM dual
     UNION
    SELECT 2 AS id, 'SUN,RAIN' AS weather
      FROM dual
     UNION
    SELECT 3 AS id, 'SUN,RAIN,SLEET' AS weather
      FROM dual
)
SELECT * FROM (
     SELECT DISTINCT id, weather, REGEXP_SUBSTR(weather, '[^,]+', 1, LEVEL) AS weather_part
       FROM t1
    CONNECT BY REGEXP_SUBSTR(weather, '[^,]+', 1, LEVEL) IS NOT NULL
) WHERE weather_part NOT IN ('SUN','RAIN','SNOW')

See the following thread on StackOverflow for more information on how to split a comma-separated string using CONNECT BY: Splitting string into multiple rows in Oracle

SELECT statement where value not from enum

5 Answers5