HomeActive Query Builderhelp joining fields that aren't exactly identical

help joining fields that aren't exactly identical


I have two tables that I need to join together in my select statement. The tables have a 7 digit number, but it is stored as 8 digits in table 1 & 7 digits in table 2.

The problem is in table 1, the field is a number, but can start with an Alpha character. If it is all numeric, it adds a 0 in front of the 7 digits, but if it starts with an alpha, it places the 0 in the second position, so 1234567 gets stored as 01234567 & T123456 gets stored as T0123456.

Please help. Thanks.
Big T | email
December 23 2009, 10:08 AM
Dear T.

Depending on your database server, you should seek for a function that return substrings ("substr" for example). Please refer to the SQL reference of your database server to cope with this task.
Sergey Smagin | email
17 hours, 28 mins since original post
Thanks. I am able to get the substring part to work, but not the if/then part of it. Depending upon the first digit of the field in the first table, it links differently. How do I accomplish that?
Big T | email
4 days, 23 hours since original post
This topic is closed