A question concerning LIKE

Database
Enthusiast

A question concerning LIKE

Hello everyone !

I create this topic because I have a question here.

I have two tables T1 and T2.

The field which is the primary key of T1 is PK1.

The field which is the primary key of T2 is PK2.

PK2 is in fact PK1 with a suffix after, something like "-1".

I wanted to know how could I perform a link between T1 and T2.

I tried this :

SELECT *

FROM T1, T2

WHERE PK2 LIKE PK1 || '%'

But it does not work.

I was wondering how it was possible to handle this situation. I thought about an "explode" function or a "split" function based on the "-" character, but I cannot find if they exist or not in Teradata.

May you help me on this topic, please ?

Best regards.

Gwenael Le Barzic

2 REPLIES
Junior Contributor

Re: A question concerning LIKE

Hi Gwenael,

are there trailing blanks in PK1 or PK2, are they defined as CHAR or VARCHAR?

The LIKE might also return wrong rows, when PK1 is not fixed length, e.g. 'abcd-1' LIKE 'abc' || '%'.

To extract the first part up to the suffix you might use SUBSTRING:

substring(p1 from 1 for position('-' in P1)-1)

If the '-' might be missing you have to put it into a CASE.

In TD14 there are built-in functions to split strings, e.g. STRTOK, but all those solutions will not fix the root cause of your problem: bad database design.

The suffix should be in a seperate column, so both tables can share the same PI, otherwise you'll never get fast joins.

Dieter


Enthusiast

Re: A question concerning LIKE

Hey Dieter.

Thank you for your answer.

Indeed, I just changed the PK in order to have something which match exactly, more efficient.

I asked just to know more how Teradata handle string manipulation. :)

Thank you again.

Best regards.

Gwenael Le Barzic