Join between two tables were the join columns data, doesn't match.

[rogerzebra]

Hi,
I would appreciate if someone could give some advice on how to solve a join between two tables, were the columns that supposed to be joined has different length and with trailing spaces in different places. All help or some good advice would be much appreciated. I have tried with substring and trimming the data but, it has too many variations and seems to be too difficult. Any thoughts?
Thanks in advance.

 

[r937]

that's what i would've suggested -- trimming and substringing

how about just removing all spaces from both values?



r937.com | rudy.ca

 

[monksnake]

Are you allowed to change the table definitions? If so, make those columns the same data type and length, and by all means get those spaces out of there!!!

I deal with spaces in tables all the time, and it drives me nuts, if our "DBA" would do something, we wouldn't have to code around stuff like that.

[small]"I see pretty girls everywhere I look, everywhere I look, everywhere I look. - Band song on movie "The Ringer"[/small]
<.

 

[SQLSister]

If you can't change the structure, then I would suggest putting the records you areinterestedin into temp tables or table variables and then using the various processes you need to create a field that you will use as a join. Then don't do the actual join until you have done this processing. Think of the time it will take to do all these substrings, etc against every row of the table wehich it would need to do inthe join. If you can select some subset out first into a temp table then at least you are only doing this on a limited group of records.

Also it is a possibility that you could add a column to the table that has the records changed on insert or update through a trigger and then at least you are doing the convert only once. Then use this column to do the join.

Questions about posting. See faq183-874

 

[rogerzebra]

Thanks guys for your reponds. Yes, one problem is as you mentioned monksnake, the data lenght are totally different between the two tables and the trailing spaces is nothing our DBA can do anything about at least in one of the tables, but for some reason the table we have control over has spaces in the data too. The other table though is retrieved from different systems outside our control and it spaces, dashes and god nows what in a column that suppose to be join able??? So this will obviously not going to work.

They have mentioned that they would be happy with a close guess. What, I'm in the end want out of this is an average price of each vehicle in our database. I got the advice to create a lookup table and use the comparable columns, and somehow calculate an average between the two column pairs and query against that. I haven't done something like that though, so I need to read up about it, or if you have an idea how that would work would make my day.
Thanks again.

 

[rogerzebra]

Thank you SQLSister, I was writing on my post when you posted yours. I think the way you describe it is the way to go. It is a big mess though and very time consuming, I'll give it a shot and see what I end up with.
Thanks

 

[bboffin]

As a close guess is sufficient why not try the Soundex function in the join i.e.
... ON Soundex(T1.Key) = Soundex(T2.Key)

It might give you too many matches but it'll probably find the genuine ones along the way and you can add smarter code to try to sort out which one you really want if you get multiple matches.

Bob Boffin