By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,459 Members | 2,284 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,459 IT Pros & Developers. It's quick & easy.

Text Formatting: Leading Zeros and Trailing Letters, maybe both or neither.

P: 3
I am attempting to write a query to combine 5 tables into a useful report. The 5 tables come from 5 different data sources.
The foreign key is an serial number, but each data source may have a different format.

For example

Table C and T have serials formatted like:
01234
56789
00123

The same serials in tables M and O would like this
1234
56789
123

As a byproduct of the data sources sometimes the serial numbers look like
0123AB in C, T
123AB in M, O

Depending on which store I look at the numbers can be different lengths:
01234 vs 0123456789

I've run into a wall in my thinking for how to get the serial numbers formatted in a way that I can use them in my query. I'd like to use the same query for different stores.

I tried shaving the leading 0s from C, T, and S but performance was hugely impacted (orders of magnitude, why is a secondary question actually)
I used Replace(LTrim(Replace(Serial,"0"," "))," ","0")

Thanks for the help.
Matt
Apr 4 '17 #1

✓ answered by NeoPa

Matt:
I tried shaving the leading 0s from C, T, and S but performance was hugely impacted (orders of magnitude, why is a secondary question actually)
That's about sargability (Sargable).

As for your main question I suspect you'll either need to convert the data in the various tables into a consistent format (It would have to be one that's compatible with all so the widest numerics plus the alphas.) or convert them in the query itself and lose sargability along with performance.

Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,398
Matt:
I tried shaving the leading 0s from C, T, and S but performance was hugely impacted (orders of magnitude, why is a secondary question actually)
That's about sargability (Sargable).

As for your main question I suspect you'll either need to convert the data in the various tables into a consistent format (It would have to be one that's compatible with all so the widest numerics plus the alphas.) or convert them in the query itself and lose sargability along with performance.
Apr 5 '17 #2

PhilOfWalton
Expert 100+
P: 1,430
Would it help to create a new INDEXED field in each table with the value of
Trim(Replace(Serial,"0","")).

Obviously you would need to run an Update query on your 5 tables.
Depending on where the information is obtained from (I presume a form), the new field could be calculated as the Serial No is updated

Phil
Apr 5 '17 #3

P: 3
Phil and Neo, I think we're on to something. Thanks for your quick reply.

I think my solution stated in the question was to remove the leading zeros from the data where they existed.
I made a new query with fields:
Serial|Serial Prime|... Data...|
Where Serial Prime is stripped of the leading zeros and is used as the foreign key.

Regarding Neo's thoughts about sargability- If I don't make a table with 'Serial Prime' and just depend on it being in a query is that what is making it non sargable?

The original data is in linked Excel sheets so I can't directly update them, but I could make a new table.
Apr 5 '17 #4

PhilOfWalton
Expert 100+
P: 1,430
I suspect that creating new tables from the Excel sheets is the way forward. The Append / Make Table query would add the new field at the same time. Unless the tables are enormous, I can't see any performance issues

Phil
Apr 5 '17 #5

NeoPa
Expert Mod 15k+
P: 31,398
PhilofWalton:
Would it help to create a new INDEXED field in each table with the value of
Trim(Replace(Serial,"0","")).
That was basically what I was saying Phil, but beware the idea of losing information as I tried to warn against. You're better off making each of the fields the same length, which would have to be the maximum needed across the board rather than the minimum. IE. Zero-fill at the left rather than chop and trim.

Access doesn't like to try to link tables together where the index fields are not the same type and length. It can be done in queries, but you know you then lose your sargability.
Apr 6 '17 #6

PhilOfWalton
Expert 100+
P: 1,430
@NeoPa

You're right as usual, but I'm not sure why you want to fill on the left rather than the right. Right filled Serial would "look" more like the original.

Phil
Apr 6 '17 #7

NeoPa
Expert Mod 15k+
P: 31,398
Consider the following values in separate tables that are all equivalent :
123
00123
0123X

These would all be converted to 000123X if the maximum numeric width were six and an 'X' is required. If stuff were added to the right instead then they wouldn't appear equal :
12300X
00123X
01230X

None of these would match the others yet the purpose is to ensure they do. After all, numerically they should all reflect 123.
Apr 6 '17 #8

Post your reply

Sign in to post your reply or Sign up for a free account.