Skip to main content
Question

Partial Match on Inline Querier


Hi, I am trying to perform a LEFT JOIN inside InlineQuerier.

I have two files with similarly named tables, (except for a _).

Inside my TABLE A, in the "CITYCODE" column, I have some values, for example: 

"LATAM - RX12345 - SIE0950331501 - Monclova - CITY SERVICE"

On the other hand, in my TABLE B, in the column of "_CITYCODE" with the value "Monclova".

 

Just like this case, there are thousands more in my tables with the same structure, what I want is to be able to do a LEFT JOIN that partially matches a complete text string containing the values of my column "_CITYCODE".

 

I have tried to do a LEFT JOIN like this:

 

---------------------------------------------------

select * from "TABLE A" 

LEFT JOIN "TABLE B"

 

on "CITYCODE" like '%'+"_CITYCODE "+'%'

--------------------------------------------------

 

But I don't get the '%' as a wildcard for a partial match.

 

Could someone help me with this?

 

 

5 replies

takashi
Evangelist
  • April 20, 2021

Hi @diello​ , I think you have to:

- use || operator to concatenate strings

- qualify column name with a table name or its alias.

 

Example:

select * from "TABLE A"
LEFT JOIN "TABLE B"
on "TABLE A"."CITYCODE" like '%'||"TABLE B"."_CITYCODE"||'%'

or

select * from "TABLE A" as a
LEFT JOIN "TABLE B" as b
on a."CITYCODE" like '%'||b."_CITYCODE"||'%'

 


  • Author
  • April 20, 2021
takashi wrote:

Hi @diello​ , I think you have to:

- use || operator to concatenate strings

- qualify column name with a table name or its alias.

 

Example:

select * from "TABLE A"
LEFT JOIN "TABLE B"
on "TABLE A"."CITYCODE" like '%'||"TABLE B"."_CITYCODE"||'%'

or

select * from "TABLE A" as a
LEFT JOIN "TABLE B" as b
on a."CITYCODE" like '%'||b."_CITYCODE"||'%'

 

it works so well.

Perform multiple left joins in a query, could you tell me how to do it?


  • Author
  • April 21, 2021

@Takashi Iijima​ Hello again, now I have a problem, the _CITYCODE column does not always have data, and many times the field is empty, how could I solve the LEFT JOIN performing it only when it has information?


takashi
Evangelist
  • April 21, 2021
diello wrote:

@Takashi Iijima​ Hello again, now I have a problem, the _CITYCODE column does not always have data, and many times the field is empty, how could I solve the LEFT JOIN performing it only when it has information?

A quick way is to insert a Tester before the TABLE B port of the InlineQuerier in order to filter out empty _CITYCODE​.

 

insert-tester-before-inlinequerier 


Forum|alt.badge.img+2

InlineQuerier uses SQLite as the underlying database. So you have to use SQLite compatible SQL. Great resources on SQLite are SQLite Tutorial & SQL As Understood By SQLite

There are also some tips n' ticks on data joins and InlineQuerier in the webinar Data Integration Basics: Merging & Joining Data

 

 

 


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings