Excluding symbols from strings-CodePudding

I have to filter one column (Product_Name) but I need to show only results which does not include symbols '_' and '<'

any ideas?

SELECT p.PRODUCT_NAME,oi.QUANTITY as "TOTAL_QUANTITY",o.ORDER_MODE
from PRODUCT_INFORMATION p
 join ORDER_ITEMS oi
 on p.PRODUCT_ID = oi.PRODUCT_ID
 join ORDERS o
 on oi.ORDER_ID = o.ORDER_ID
 where p.PRODUCT_NAME NOT IN ('<','(_)')
group by p.PRODUCT_NAME,o.ORDER_MODE,p.PRODUCT_ID,oi.PRODUCT_ID,oi.ORDER_ID,o.ORDER_ID,oi.QUANTITY
having oi.QUANTITY >= 200;

CodePudding user response：

instr function might be one option:

where instr(p.product_name, '<') = 0
  and instr(p.product_name, '_') = 0

CodePudding user response：

You can use NOT LIKE:

SELECT p.PRODUCT_NAME,
       oi.QUANTITY as "TOTAL_QUANTITY",
       o.ORDER_MODE
from   PRODUCT_INFORMATION p
       join ORDER_ITEMS oi
       on p.PRODUCT_ID = oi.PRODUCT_ID
       join ORDERS o
       on oi.ORDER_ID = o.ORDER_ID
where  p.PRODUCT_NAME NOT LIKE '%<%'
AND    p.PRODUCT_NAME NOT LIKE '%_%'
group by
       p.PRODUCT_NAME,
       o.ORDER_MODE,
       p.PRODUCT_ID,
       oi.PRODUCT_ID,
       oi.ORDER_ID,
       o.ORDER_ID,
       oi.QUANTITY
having oi.QUANTITY >= 200;

or TRANSLATE:

SELECT p.PRODUCT_NAME,
       oi.QUANTITY as "TOTAL_QUANTITY",
       o.ORDER_MODE
from   PRODUCT_INFORMATION p
       join ORDER_ITEMS oi
       on p.PRODUCT_ID = oi.PRODUCT_ID
       join ORDERS o
       on oi.ORDER_ID = o.ORDER_ID
where  p.PRODUCT_NAME = TRANSLATE(p.PRODUCT_NAME, 'A<_', 'A')
group by
       p.PRODUCT_NAME,
       o.ORDER_MODE,
       p.PRODUCT_ID,
       oi.PRODUCT_ID,
       oi.ORDER_ID,
       o.ORDER_ID,
       oi.QUANTITY
having oi.QUANTITY >= 200;