How to search a string inside a string in Oracle

Question

I have a table A with columns Country ID ,Country. and data is like this

US United States
IN India
JP Japan
NP Nepal

etc . I have different table B which has a column Country which mostly has free text data . Data is like

Texas United States 
India KA
XYS Japan WYS 
EverestNepal  
XYZ

etc.

What i want is that if country column in Table B has a country matching from any column of country in Table A , it should return Country from Table A.

So for the example i gave Table B has Texas United States --- there is a match in Table A with 'United States' : It should print United States

Table B has India KA ---- there is a match in Table A with 'India':it should print India

EverestNepal --- there is a match in Table A with 'Nepal': it should print Nepal Table B has and so on.

To summarize : If the exact match of country in Table B from anywhere in the string is found in Table A, it should print country from Table A I dont think Like , IN , Substring will work in such situation

Littlefoot · Accepted Answer · 2019-08-06 06:10:20Z

INSTR (line #17) is what you probably need.

SQL> with
  2  ta (country_id, country) as
  3    (select 'US', 'United States' from dual union all
  4     select 'IN', 'India'         from dual union all
  5     select 'JP', 'Japan'         from dual union all
  6     select 'NP', 'Nepal'         from dual
  7    ),
  8  tb (country) as
  9    (select 'Texas United States' from dual union all
 10     select 'India KA'            from dual union all
 11     select 'XYS Japan WYS'       from dual union all
 12     select 'EverestNepal'        from dual union all
 13     select 'XYZ'                 from dual
 14    )
 15  select b.country B_country,
 16         a.country A_country
 17  from ta a right join tb b on instr(b.country, a.country) > 0;

B_COUNTRY           A_COUNTRY
------------------- -------------
Texas United States United States
India KA            India
XYS Japan WYS       Japan
EverestNepal        Nepal
XYZ

SQL>

Popeye · Accepted Answer · 2019-08-06 06:10:10Z

0

You can use like operator to join between the tables:

SELECT
    A.COUNTRY
FROM
    TABLE_A A
    JOIN TABLE_B B ON ( A.COUNTRY LIKE '%'
                                       || B.COUNTRY
                                       || '%' );

Cheers!!

answered Aug 6, 2019 at 6:10

Popeye

36k4 gold badges12 silver badges31 bronze badges

Collectives™ on Stack Overflow

How to search a string inside a string in Oracle

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related