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.cu stomer_no char(15) right justified, zero padded
sample customer numbers:
000000000000001
000000000000010
000000000000234
000000000012345

db2.customer.cu stomer_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]

Re: Join zero padded char to varchar

You can do:

1). db1.customer.cu stomer_no = RIGHT('00000000 0000000' +
db2.customer.cu stomer_no, 15)

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

2). CAST(CAST(db1.c ustomer.custome r_no AS INT) AS VARCHAR(20)) =
db2.customer.cu stomer_no

This one will not use index on db1.customer.cu stomer_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]

Re: Join zero padded char to varchar

Thank you. Both options work.


"Plamen Ratchev" <Plamen@SQLStud io.comwrote in message
news:DNSdnb-c1-0DG9zVnZ2dnUVZ_ hzinZ2d@speakea sy.net...

You can do:
>
1). db1.customer.cu stomer_no = RIGHT('00000000 0000000' +
db2.customer.cu stomer_no, 15)
>
The above will no be able to utilize index on db2.customer.cu stomer_no.
>
2). CAST(CAST(db1.c ustomer.custome r_no AS INT) AS VARCHAR(20)) =
db2.customer.cu stomer_no
>
This one will not use index on db1.customer.cu stomer_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