Join on Substring

[Former Member]

Hi All,

I have two tables I would like to join. Table 1's PK is a number, Table 2's PK is identical to Table 1, but with a character at the front:

Table 1 PK	Table 2 PK
123	J123
124	X124
125	J125
126	K126
127	X127

How can you join on a substring in Information Steward's view screen?

Thanks,

Katherine

[Former Member · Accepted solution]

Katherine,

You can use substr function to retrieve a specific portion of string in your join. Here is the function usage and examples

[Former Member]

Thanks guys! Worked like a charm.

[former_member186897]

Katherine Leenhouts,

This is simple. You can directly do this in where clause. Considering your field name in both tables are ID.

Table_1.ID=cast( substr(Table_2.ID,2,length(Table_2.ID )),'INT')

• substr : it will read string from 2nd character till end.
• Cast is being used to suppress the warding message in case first table field ID is in int. You can remove casting in case your both tables have varchar for these fields.

Hope this helps.

Regards,

Ansari MS