Database
Enthusiast

## problem joining on imported unix column

Table A has the value `'abc123'` Table B has the value `'abc123'`

I confirm this by

``SELECT * FROM tableA a WHERE value='abc123'andSELECT * FROM tableB b WHERE value='abc123'``

and both queries return results

however when i do:

``select    a.*from    tablea ajoin    tableb bon trim(trailing from a.value) = trim(trailing from b.value)``

nothing returns.

now the string I am dealing with in one of the these tables was imported over from unix and I made sure to strip out the whitespace, etc. but I can't figure out what I'm doing wrong.

Thru char_length, I've also verified that the column in question in each table is 32 chars in length.

Tags (3)
9 REPLIES
Junior Contributor

## Re: problem joining on imported unix column

Strange,

what is returned when you UNION both values?

`SELECT value, char2hexint(value) FROM tableA a WHERE value='abc123'UNIONSELECT value, char2hexint(value) FROM tableB b WHERE value='abc123'`

Dieter

Enthusiast

## Re: problem joining on imported unix column

Dieter:

That does work!  What could be the difference between 'abc123' and 'abc123' that is stored in a teradata table?

Junior Contributor

## Re: problem joining on imported unix column

"does work" is almost as good as "doesn't work" :-)

What is returned by that query? One row or two rows? And for UNION ALL?

Can you show the actual output?

Btw, you never need to join on TRIM(TRAILING) as string comparison does't care about trailing blanks. But a leading blank would matter, you might try TRIM(BOTH) instead.

Dieter

Enthusiast

## Re: problem joining on imported unix column

Dieter:

One row is returned.  Here is the output:

3230376239303736313430306135663134343037343536376662616333623037                                                                                                                                                                                                                                                                                                                                                   again it's a 32 varchar string I'm trying to match up.

thanks for the tip about trim.

derek

Enthusiast

## Re: problem joining on imported unix column

...and union all produces 15 rows exactly like the string above.

Junior Contributor

## Re: problem joining on imported unix column

Hi Derek,

so you're searching for '207b90761400a5f144074567fbac3b07'?

And this exists in both tables? For 100% shure?

In the beginning i thought there might have been '20'xb and '80xb', space and no-break space, or non-printing chars which look the same, but these are only latin chars and digits.

You might open an incident.

Dieter

Enthusiast

## Re: problem joining on imported unix column

Dieter:

thanks for the quick reply.  yes, I am searching for

 207b90761400a5f144074567fbac3b07

I'm sure (or as sure as can be) b/c when I search for the string (ie select * from tablea where val=

 207b90761400a5f144074567fbac3b07i get back a result.
Junior Contributor

## Re: problem joining on imported unix column

If it exists in both tables following SQL should return exactly one row:

`select * from   (select val from tablea where val= '207b90761400a5f144074567fbac3b07') as afull join  (select val from tableb where val= '207b90761400a5f144074567fbac3b07') as bon a.val = b.val`

Otherwise i don't know...

Dieter

## Re: problem joining on imported unix column

Could you share the column definitions from the table? I'm wondering about differences in character set, upper/lower case attributes,...