Re: Finding rows with text columns beginning with other text columns

Am 10.05.2010 11:50 schrieb Alban Hertroys:
> On 10 May 2010, at 24:01, Christoph Zwerschke wrote:
>
>> select * from b join a on b.txt like a.txt||'%'
>>
>> I feel there should be a performat way to query these entries,
>> but I can't come up with anything. Can anybody help me?
>
> Have you tried using substring instead of like?

How exactly? I tried this:

substr(b.txt, 1, length(a.txt)) = a.txt

but it cannot be optimized and results in a nested loop, too.

It only works with a fixed length:

substr(b.txt, 1, 3) = a.txt

So theoretically I could do something like

select * from b join a
on substr(b.txt, 1, 1) = a.txt and length(b.txt) = 1
union select * from b join a
on substr(b.txt, 1, 2) = a.txt and length(b.txt) = 2
union select * from b join a
on substr(b.txt, 1, 3) = a.txt and length(b.txt) = 3
union ...

... up to the maximum possible string length in a.txt. Not very elegant.

If the question is not finding text cols in b starting with text cols in
a, but text cols in b starting with text cols in a as their first word,
then the following join condition works very well:

split_part(b.txt, ' ', 1) = a.txt

But I'm still looking for a simple solution to the original problem.

-- Christoph