Visual Query Builder--Connection on part of another field

Okay. I'm trying to use visual query builder and I need to connect two tables on a location #. The issue that I have is that one table has the location #plus the number17 at the end. Son on table 1 location number it is listed as 1234 but on table 2 locatin number is listed as 123417. How can I make the connection?

I'm thinking that it would be this connection but not sure how:

left outer join table2.location_number on table1.location_number = table2.location_number

Any ideas?

Help

Accepted answers

OllieClarke

Hi @cowannbell

I tested this on my MSSql Server and it worked fine

All comments

TUSHAR050392

Hey @cowannbell, Have you tried joining the two tables using substring. So you can use the substring command to get rid of the 17 from your one dataset and join on field from other one. It will be like below-

left outer join table2.location_number on table1.location_number = substring(table2.location_number, 0, LEN(table2.location_number)-1)

In place of substring, you can also try LEFT(table2.location_number, LEN(table2.location_number)-2)

Let me know if it was helpful.

cowannbell

I wanted to use the left function but this is a numeric field. I need to convert it but I wasn't quite sure how to use left and convert to numeric at the same time.

OllieClarke

Hi @cowannbell

I tested this on my MSSql Server and it worked fine

cowannbell

Thank you so much. This did the trick.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703