CODEWITHSUNDEEP
sqloracle-database
Giorgos
Giorgos

Reputation: 677

Oracle like Pattern-matching LIKE Condition

I need to use a column in a like Condition like following 'UPDATE'

 UPDATE CUSTOMER_TRANSACTIONS ct
 SET PROCESSID = vQUARTER 
 where ct.CUSTOMERID = l_arrc1(i).CUSTOMERID 
 and ct.APPOINTOFCAID = l_arrc1(i).APPOINTOFCAID        
 and ct.INVOICEID like "'"||l_arrc1(i).INVOICEID||%||"'";

instead

UPDATE CUSTOMER_TRANSACTIONS ct
SET PROCESSID = '2015Q4'
where ct.CUSTOMERID = l_arrc1(i).CUSTOMERID 
and ct.APPOINTOFCAID = l_arrc1(i).APPOINTOFCAID        
and ct.INVOICEID like '0158741%';

the INVOICEID and l_arrc1(i).INVOICEID are VARCHAR

how to do that?

Upvotes: 1

Views: 157

Answers (2)

San
San

Reputation: 4538

Assuming that INVOICEID is number columns:

UPDATE customer_transactions ct
   SET processid = vQUARTER
 WHERE ct.customerid = l_arrc1(i).customerid
   AND ct.appointofcaid = l_arrc1(i).appointofcaid
   AND to_char(ct.invoiceid) LIKE to_char(l_arrc1(i).INVOICEID) || '%';

If invoiceid is not number datatype then you can remove to_char

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You are missing appropriate delimiters for the strings, single quotes:

UPDATE CUSTOMER_TRANSACTIONS ct
    SET PROCESSID = vQUARTER 
    WHERE ct.CUSTOMERID = l_arrc1(i).CUSTOMERID and
          ct.APPOINTOFCAID = l_arrc1(i).APPOINTOFCAID and      
          ct.INVOICEID LIKE l_arrc1(i).INVOICEID || '%' ;

The appropriate delimiter for strings in SQL is single quotes rather than double quotes.

Upvotes: 1

Related Questions