CODEWITHSUNDEEP
t-sqljoinnumericvariable-length
user983988
user983988

Reputation:

TSQL - Joining two numeric fields but with different lengths

Sorry if this is a basic question but I have been unable to find an answer on google etc.

I have two tables: Table1, Table2

Table 1 has a field 'ACCOUNTNO' which is a 10 character numeric field (example: 1122334455)

Table 2 has a field 'SUBACCNO' which is a 12 character numeric field (example: 112233445501)

As you can see SUBACCNO is the same as ACCOUNTNO but with an additional two digits at the end ranging from 01-99.

If I want to join the two tables and have been trying something like this:

SELECT *
FROM TABLE1
JOIN TABLE2
ON TABLE1.ACCOUNTNO = TABLE2.SUBACCNO
WHERE STR(SUBACCNO) LIKE '1122334455%%'

As a wildcard cannot be performed on numeric data I have attempted converting it to a String with a wildcard for the last two characters. However this returns nothing.

Is anyone able to offer some advice? Thanks!

Upvotes: 0

Views: 254

Answers (2)

TheGameiswar
TheGameiswar

Reputation: 28910

Your join wont work..

Try this,which is not Sargable..if you cant change your design

SELECT *
FROM TABLE1
JOIN TABLE2
ON TABLE1.ACCOUNTNO = cast(substring(TABLE2.SUBACCNO,1,10) as bigint)

you also can add one more computed column like below and index it for good performance which i recommend if you are using these type of queries a lot

create table yourtable
(
accno bigint,
accno1 as  cast(substring(accno,1,10) as bigint) persisted
)

Now you can index above column for good performance

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521794

How about joining on the SUBACCNO column divided by 100:

SELECT *
FROM TABLE1
JOIN TABLE2
    ON TABLE1.ACCOUNTNO = TABLE2.SUBACCNO / 100

Actually, to be safe you might want to explicitly truncate the quotient to the zeroth decimal place using ROUND():

SELECT *
FROM TABLE1
JOIN TABLE2
    ON ROUND(TABLE1.ACCOUNTNO, 0, 1) = ROUND(TABLE2.SUBACCNO / 100, 0, 1)

Upvotes: 2

Related Questions