Pl/sql Like

Creamy Goodness

i have a piece of pl/sql that looks like this:

DECLARE
    dest    Location.description%TYPE:=&dest;
    result    Location.description%TYPE;
    
BEGIN
    SELECT description INTO result FROM Location WHERE description LIKE '%' || dest || '%' ;    
       
    IF result LIKE dest
    THEN
        DBMS_OUTPUT.PUT_LINE('we have holidays based in ' || dest || ' contact a agent to arrange today.');
    END IF;
    
    
    EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    DBMS_OUTPUT.PUT_LINE('cannot find ' || dest || ' in the database.');
                WHEN OTHERS THEN
                    DBMS_OUTPUT.PUT_LINE('Unexpected error occurred.');    
END;

what i want it to do is enter in a destination eg. 'new york' and check it to see if it's like the result. the problem arises with the description in the Locations table is capitalised so when i enter in 'New York' it does the output line, but i want it to execute this if someone types in something like new york eg 'new yokr'. i thought the LIKE command would do this.

cousin_borat

Usually in pl/sql when comparing string you make both strings uppercase so use the pl/sql upper function

DECLARE
    dest    Location.description%TYPE:=&dest;
    result    Location.description%TYPE;
    
BEGIN
    SELECT description INTO result FROM Location [B]WHERE UPPER([/B]description[B])[/B] LIKE '%' || [B]UPPER([/B]dest[B])[/B] || '%' ;    
       
   and so on...

Hope this helps.

Also watch out for NULLvalues in your WHERE clauses as this will generate PL/SQL exceptions

Creamy Goodness

thank you kind sir.