2

I have a list of value that are allowed such as: the cat, the mouse, the dog, and the parrot. Now I want to have the possibility to add many values from this list in a cell separated with semicolon (;).

That means I can add:

The cat; The dog; The mouse

The cat; the parrot

The mouse; The cat

The parrot

But I can not add

The lion; the cat

The mouse; the parrot; the Lion

The cat;( That means I can not add a semicolon at the end)

The cat; The mouse;

;( That means i can not just add a semi colon) I try to write a constraint using this function regexp_like but this does not work fine.

not REGEXP_LIKE (animal, '[^(the cat| the mouse |the dog|the parrot|; )]', 'i')

N.B: animal is the column which I applied the constraint

Community
  • 1
  • 1
van
  • 29
  • 6
  • 2
    Don't do that. Do not store multiple values separated by some character in a single column. Read up on database normalization. Do not go down this road. –  Apr 20 '17 at 11:58

2 Answers2

1

Prepend the delimiter to the string then match like this:

REGEXP_LIKE(
  ';' || animal,
  '^(;\s*(the cat|the mouse|the dog|the parrot)\s*)*$',
  'i'
)

Update:

You could use a second table (with a foreign key reference) or a nested table:

CREATE TYPE stringlist IS TABLE OF VARCHAR2(4000);
/

CREATE TABLE animals (
  id          number,
  animal_list stringlist
) NESTED TABLE animal_list STORE AS animals__animal;

ALTER TABLE ANIMALS__ANIMAL ADD CONSTRAINT animals__animal__chk
  CHECK ( TRIM( BOTH FROM LOWER( column_value ) )
            IN ( 'the cat', 'the mouse', 'the dog', 'the parrot' ) );

INSERT INTO animals VALUES ( 1, StringList( 'the cat', ' the dog ' ) );
-- Succeeds
INSERT INTO animals VALUES ( 2, StringList( 'the cat', 'the horse' ) );
-- Fails with ORA-02290: check constraint (TEST.ANIMALS__ANIMAL__CHK) violated

Then you can do:

SELECT id,
       ( SELECT LISTAGG( COLUMN_VALUE, ';' ) WITHIN GROUP ( ORDER BY ROWNUM )
         FROM   TABLE( a.animal_list ) ) AS animals
FROM   animals a;

Which outputs:

ID ANIMALS
-- -----------------
 1 the cat; the dog 
MT0
  • 143,790
  • 11
  • 59
  • 117
  • Thanks for the answer this was helpful. But i have ommitted to indicate that i want have the possibility to insert the value null or no values. I tried to modify your answer like this regexp_like(';' || categories, '^(;\s*(the cat|the mouse|the dog|the parrot|null| |)\s*)*$', 'i'). But i can now insert null; – van Apr 20 '17 at 09:39
  • @VannessaKemeni In Oracle `NULL` and an empty string are the same thing, so you can do: `CHECK ( animal IS NULL OR REGEXP_LIKE( ... ) )` – MT0 Apr 20 '17 at 09:44
  • really not : i add the constraint animal is null but when i want top insert the value 'null' that does not work. So i have add this value in regexp_like(...). – van Apr 20 '17 at 10:11
  • In my real example i have fast16 words and oracle says that the regular expressions is too long. How i can handle that? – van Apr 20 '17 at 10:19
  • @ MT0 Your answer do exactly what i want . but the problem is that my real list of values is too long and i get the error ORA-12733: regular expression too long. And now i want to know if there another way to solve this problem without using regular expressions. – van Apr 20 '17 at 11:57
  • @VannessaKemeni not that I can find but I have added a second solution using nested tables. – MT0 Apr 20 '17 at 12:22
  • I have already tried with nested tables and JSON. But that is not appropriated for what I want to do. – van Apr 20 '17 at 12:24
0

For the regex part, I believe this is the simplest validation regex that doesn't require manipulating the data (note there is a space after ';')

^(the cat|the mouse|the dog|the parrot|; )+$
Tezra
  • 8,463
  • 3
  • 31
  • 68