Join zero padded char to varchar

[Artie]

Having a brain fart and can't figure this out...
I have 2 databases I need to join:

db1.customer.customer_no char(15) right justified, zero padded
sample customer numbers:
000000000000001
000000000000010
000000000000234
000000000012345

db2.customer.customer_no varchar(20) left justified, no padding
sample customer numbers:
1
10
234
12345

How do I join tables on customer_no? Use cast, convert? Strip zeroes from
db1 table?

Thanks.

[Plamen Ratchev]

You can do:

1). db1.customer.customer_no = RIGHT('000000000000000' +
db2.customer.customer_no, 15)

The above will no be able to utilize index on db2.customer.customer_no.

2). CAST(CAST(db1.customer.customer_no AS INT) AS VARCHAR(20)) =
db2.customer.customer_no

This one will not use index on db1.customer.customer_no.

You can create a view (or add computed column) to perform the above on one
of the tables, and then index the transformed column.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Artie]

Thank you. Both options work.
"Plamen Ratchev" <Pl****@SQLStudio.comwrote in message
news:DN******************************@speakeasy.ne t...

You can do:

1). db1.customer.customer_no = RIGHT('000000000000000' +
db2.customer.customer_no, 15)

The above will no be able to utilize index on db2.customer.customer_no.

2). CAST(CAST(db1.customer.customer_no AS INT) AS VARCHAR(20)) =
db2.customer.customer_no

This one will not use index on db1.customer.customer_no.

You can create a view (or add computed column) to perform the above on one
of the tables, and then index the transformed column.

HTH,

Plamen Ratchev
http://www.SQLStudio.com