How write this join criteria in sql?

Question

I have column_x that holds either 20 digits or 22 digits numbers, and column_Y that holds 22 digits.

If when column_x's number is 20 digits long, I want to join on column_Y with only numbers in column_Y that has the second digit NOT equal to 0. Else (when column_x number is 22 digits long), I want to join column_x with column_Y's numbers normally.

How to do this?

For example,

if column X has the numbers 00000000000579701673 (20 digits) and 0000000000000957821317 (22 digits), and column Y has the numbers 9900000000000579701673 (22 digits) and 0000000000000957821317 (22 digits), I want to join 00000000000579701673 (20 digits) from column_x with 9900000000000579701673 (22 digits) from column_Y and 0000000000000957821317 (22 digits) from column_x with 0000000000000957821317 (22 digits) from column_Y

if column_X is 20 digits the prefix is anywhere from '01' to '99' I am using teradata sql assist

Answer 1

Based on your logic you simple need to prepend '99' if the string is only 20 chars (similar to June7's answer but using Standard SQL/Teradata syntax):

join ...
on case when char_length(column_x) = 20 then '99' else '' end || column_x = column_y

Edit:

Based on your comment if X has 20 digits then it must be joined with column Y that begins with '01' or to '99' this might be what you want:

on right(column_x, 20)= right(column_y, 20)
and (char_length(column_x) = 22 
     or substr(column_y, 1, 2) <> '00')