PLSQL check two string if a substring exist in both string

Question

I have a predefined value from the parameter table varchar column which is below.

'ENGR PACKAGE TYPE,DOCUMENT TYPE,PRODUCT TYPE,MATERIAL TYPE,PACKAGE CODE'

So whenever I got a status message like this

'PACKAGE CODE "F075" NOT FOUND'

I want to check it with the column in the parameter table, then I want to have a plsql case when condition that if a string exists in the parameter table, it will return 'TRUE' else 'FALSE'.

but MY PLSQL query below is not working.

select CASE WHEN INSTR(upper('PACKAGE CODE "F075" NOT FOUND'), 'ENGR PACKAGE TYPE,DOCUMENT TYPE,PRODUCT TYPE,MATERIAL TYPE,PACKAGE CODE') > 0 THEN
                'TRUE'
            ELSE 'FALSE'
END SAMPLE from dual;

Hope you can help me

Lokoba · Accepted Answer · 2021-06-02 05:48:21Z

Your query will return True in the following way.

select 
    case 
        when instr(upper('PACKAGE CODE "F075" NOT FOUND'), 'PACKAGE CODE') > 0 then
            'TRUE'
        else 
            'FALSE'
    end sample 
from 
    dual;

If you wanna check all variants as constant values then use the following query

select 
    case 
        when instr(upper('PACKAGE CODE "F075" NOT FOUND'), 'PACKAGE CODE') > 0 then
            'TRUE'
        when instr(upper('PACKAGE CODE "F075" NOT FOUND'), 'ENGR PACKAGE TYPE') > 0 then
            'TRUE'
        when instr(upper('PACKAGE CODE "F075" NOT FOUND'), 'DOCUMENT TYPE') > 0 then
            'TRUE'
        when instr(upper('PACKAGE CODE "F075" NOT FOUND'), 'PRODUCT TYPE') > 0 then
            'TRUE'
        when instr(upper('PACKAGE CODE "F075" NOT FOUND'), 'MATERIAL TYPE') > 0 then
            'TRUE'
        else 
            'FALSE'
    end sample 
from 
    dual;

Littlefoot · Accepted Answer · 2021-06-02 20:35:04Z

0

Another option:

SQL> with
  2  sample (col) as
  3    (select 'PACKAGE CODE "F075" NOT FOUND' from dual),
  4  param (col) as
  5    (select * from table(sys.odcivarchar2list('ENGR PACKAGE TYPE','DOCUMENT TYPE','PRODUCT TYPE','MATERIAL TYPE','PACKAGE CODE')))
  6  select max(case when instr(s.col, p.col) > 0 then 'true'
  7                  else 'false'
  8             end) as result
  9  from sample s cross join param p;

RESUL
-----
true

SQL>

answered Jun 2, 2021 at 20:35

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Comments

Vinz Sumampong · Accepted Answer · 2021-06-03 06:04:02Z

0

figure this out using regexp like function. feels like a charm.

select CASE WHEN regexp_like(upper('Case Outline PACKAGE CODE "948H-01" not found'), 'ENGR PACKAGE TYPE|DOCUMENT TYPE|PRODUCT TYPE|MATERIAL TYPE|PACKAGE CODE')
THEN 'TRUE'
ELSE 'FALSE' END from dual;

answered Jun 3, 2021 at 6:04

Vinz Sumampong

11 bronze badge

Collectives™ on Stack Overflow

PLSQL check two string if a substring exist in both string

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related