Difficult Joining 1

[CrystalVis]

I need to join two tables by account_id field. Where the account_id in the second table (let call it table B) is populated with the account_id from the first table (let call it table A), a colon and one digit. For example:
acount_id in table A is 1234567, there are some processing done in the backend and then account_id in table B is populated as follow: 1234567:1.

I need to join these two table on account_id. This is what I have so far

SELECT
A.Name,
A.PhoneNumber,
B.Duration
B.Add_Dtm
From A
Join B
ON A.account_id = SUBSTRING(B.account_id,1,7)

This query will give me the desire result as long as the account_id is 7 characters long. However, the account_id will varies (some accounts are 8 characters long others are 9 characters long), which will mess up the join statement. What I try to accomplish is match the account_id in table A with the account_id in table B up to the position before the colon. I hope my description make sense. If not, please ask. Your help is greatly appreciated.
TIA
by the way, the data type of the account_id field is varchar.

 

[fluteplr]

1. Your database design is bad. What you have in the account # field in table B should be in two fields.

That being said, this should work but the performance will probably not be good.

A.account_id =
left(b.account_id,charindex(':',b.accountid)-1)

 

[CrystalVis]

Fluteplr,
Thank you for your quick respond. I agree with you this was a very freaking bad design. As a matter of fact, the person who designed the schema has left the company. Now we stuck with it. When I try your suggestion, I receive the following error message: "Invalid length parameter passed to the substring function". I have not use the charinex before. Can you please explain what this statement does left(b.account_id,charindex(':',b.accountid)-1) and why I got the error message. Thanks

 

[fluteplr]

Is it possible you have keys in table b without the ':'?

What the code does is the following.

Charindex finds the position of the first occurance of the fist parameter within the second parameter.

So charindex(':','99999:9')
should return 6.

The problem would be if there are rows with no ':' give me a bit and I will try to figure out how to handle that case.

 

[fluteplr]

Try

A.account_id =
case when charindex(':',b.account_id) > 0 then left(@x,charindex(':',b.account_id)-1) else b.account_id end

 

[CrystalVis]

fluteplr,
You're absolutely right...I just research the data in this field and it turn out that it was also populated with phone number. So what I did is select data only if the len of the account_id is less then 10. It seems to work but not a solid solution because we might have account_id that is 10 or longer. The application was design to allow the users different way to login and perform some task. It depends on the site the users login one site use phone # the other user account_id. That is how the account_id in table B get populate. I'll try your other suggestion. Thank you so much for the explanation.