By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,379 Members | 1,380 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,379 IT Pros & Developers. It's quick & easy.

Join zero padded char to varchar

P: n/a
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.
May 31 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

May 31 '08 #2

P: n/a
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

Jun 1 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.