Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

Join with like/ilike

Hi,

I've two tables.
The first table contains a varchar column with any text.
The second table contains also a varchar column with some fixed entries.
The entries of the second table can occur in the first table in the specified column, but they can also be only a part of the text.

Example

Table A:
abc
abcd
abcde
cdef
cdefg

Table B:
bcd
fg

I want to select only the matching entries from table A:
abcd
abcde
cdefg

In the join component I have the following expression:
"table_a". "text" ilike '%'||"table_b". "text"||'%'

Unfortunately this does not work and the resultset is empty.

Is the syntax different in Redshift? (I couldn't find anything).

Or doesn't it work the way I want it to?

Best,
Daniel

2 Community Answers

Matillion Agent  

Dan D'Orazio —

Hi Daniel -

I setup a test data set using the example you provided about and I was able to create the result you’re looking for. If you can open a new case via e-mail (support@matillion.com) I can export it and share it with you.

Best -
Dan
=


Daniel Petri —

Hi Dan,

shame on me, I missed something.
This works: "table_a". "text" ilike '%'||trim("table_b". "text")||'%'

Best,
Daniel

Post Your Community Answer

To add an answer please login