How to unescape % in LIKE clause-CodePudding

I have my search patterns stored in database in patterns table. For example my table column name_pattern contains string 'Basic%'. I'd like to create dynamic search where search patterns will be fetched from name_pattern column.

So my SQL query should look something like:

SELECT *
FROM products
WHERE product_name LIKE name_pattern <-- somehow joined from patterns table

Seems that Oracle escapes % in my string but I want to take it unescapped in order my query to work like:

SELECT *
FROM products
WHERE product_name LIKE 'Basic%'

I found that my problem is with stable set of rows:

CREATE TABLE patterns(code CHAR(1),name_pattern VARCHAR2(20));
INSERT INTO patterns(code,name_pattern) VALUES('B','Basic%');
INSERT INTO patterns(code,name_pattern) VALUES('T','%thing');

CREATE TABLE products (id NUMBER,name VARCHAR2(20),code CHAR(1));
INSERT INTO products(id,name,found) VALUES(1,'Basic instinct',NULL);
INSERT INTO products(id,name,found) VALUES(2,'Basic thing',NULL);
INSERT INTO products(id,name,found) VALUES(3,'Super thing',NULL);
INSERT INTO products(id,name,found) VALUES(4,'Hyper instinct',NULL);

MERGE INTO products p USING
(
   SELECT code,name_pattern FROM patterns
) s
ON (p.name LIKE s.name_pattern)
WHEN MATCHED THEN UPDATE SET p.code=s.code;

SELECT * FROM products;

If my search patterns were Basic% and Super% in patterns table then this MERGE will work, but if my search patterns are Basic% and %thing, the second product should be marked with both codes 'B' and 'T' and that causes the error:

ORA-30926: unable to get a stable set of rows in the source tables

So my problem is not in (un)escaping :-(, sorry

CodePudding user response：

You don't have to (un)escape anything, I'd say.

SQL> with
  2  patterns (name_pattern) as
  3    (select 'Basic%'    from dual union all
  4     select '%foot%'    from dual
  5    ),
  6  products (id, name) as
  7    (select 1, 'Basic instinct' from dual union all
  8     select 2, 'Visual Basic'   from dual union all
  9     select 3, 'Littlefoot'     from dual union all
 10     select 4, 'Happy feet'     from dual
 11    )
 12  select b.id, b.name, a.name_pattern
 13  from products b join patterns a on b.name like a.name_pattern;

        ID NAME           NAME_P
---------- -------------- ------
         1 Basic instinct Basic%
         3 Littlefoot     %foot%

SQL>

Based on test case you provided: don't merge, update!

SQL> update products p set
  2    p.found = 1
  3    where exists (select null
  4                  from patterns o
  5                  where p.name like o.name_pattern
  6                 );

3 rows updated.

SQL> select * from products;

        ID NAME                      FOUND
---------- -------------------- ----------
         1 Basic instinct                1
         2 Basic thing                   1
         3 Super thing                   1
         4 Hyper instinct                0

SQL>

After you changed your mind (again), it is still update. Though, you didn't explain which code you want to take when there's multiple match (for example, product 2 matches both "Basic%" and "%thing") so I took any of them, using the min function.

SQL> update products p set
  2    p.code = (select min(o.code)
  3              from patterns o
  4              where p.name like o.name_pattern
  5             );

4 rows updated.

SQL> select * from products;

        ID NAME                 CODE
---------- -------------------- ----------
         1 Basic instinct       B
         2 Basic thing          B
         3 Super thing          T
         4 Hyper instinct       NULL

SQL>