Search code examples
sqloraclesql-like

How can I get return value for partially matching another columns in SQL?

In plsql for the below query:

Select b.geo, b.country,a.country 
From table_a a
Left join table_b b 
On LTRIM(RTRIM(UPPER(b.country))) = LTRIM(RTRIM(UPPER(a.country)))

Suppose this above query is returning output as

  1. LATAM Colombia Colombia
  2. Null null Colombia_group1

I need for Colombia_group1 it should return LATAM.

Solution

  • You can use LIKE:

    Select b.geo, b.country,a.country 
From   table_a a
       LEFT OUTER JOIN table_b b 
       ON TRIM(UPPER(a.country)) LIKE TRIM(UPPER(b.country)) || '%'

    Which, for the sample data:

    CREATE TABLE table_a ( country ) AS
SELECT 'Colombia' FROM DUAL UNION ALL
SELECT 'Colombia_group1' FROM DUAL;

CREATE TABLE table_b ( country, geo ) AS
SELECT 'Colombia', 'LATAM' FROM DUAL;

    Outputs:

    GEO   | COUNTRY  | COUNTRY        
:---- | :------- | :--------------
LATAM | Colombia | Colombia       
LATAM | Colombia | Colombia_group1

    db<>fiddle here