Looking for multiple strings in variable stored procedure

Justine 0
Viewed 26

I'm trying to write a stored procedure for cloned databases. I want to check the database_name variable for a specific string. Currently, this is what I've got:

IF ((CONTAINS(:database_name, 'STRING1'))=TRUE) THEN
        RAISE clone_exception;
END IF;

    IF ((CONTAINS(:database_name, 'STRING2'))=TRUE) THEN
        RAISE clone_exception;
END IF;

IF ((CONTAINS(:database_name, 'STRING3'))=TRUE) THEN
        RAISE clone_exception;
END IF;

I don't want to have to write 3 CONTAIN blocks I'd rather keep it compact and clean, is there a way I could get all 3 STRING checks in the same block of code? I've looked into using the IN operator but I can't find documentation that doesn't have examples using only a table query which doesn't really help me since this conditional is comparing only a variable and not a table column.

stored-procedures snowflake-cloud-data-platform sql
1 Answers

It is easy to achieve with LIKE ANY:

DECLARE 
    database_name TEXT := '...';
BEGIN
   IF (:database_name ILIKE ANY ('%STRING1%','%STRING2%','%STRING3%') THEN
    RAISE clone_exception:
   END IF;
END;
Lukasz Szozda0
Related