Repacing Alpha Characters with Numbers in a String
Experts,
In MS Access, if I have a 10 character Cost Center field formatted as Text, and I want to replace the letters with numerals so I can convert the field to a numeric data type...how would I go about executing a find/replace operation so that the letter A would be replaced by a 1, the letter B would be replaced by the number 2, etc.?
The syntax of the current data is (where # is a number and ? is a letter)
####?####, or
####?###?
Thanks
In MS Access, if I have a 10 character Cost Center field formatted as Text, and I want to replace the letters with numerals so I can convert the field to a numeric data type...how would I go about executing a find/replace operation so that the letter A would be replaced by a 1, the letter B would be replaced by the number 2, etc.?
The syntax of the current data is (where # is a number and ? is a letter)
####?####, or
####?###?
Thanks
If you're replacing "A" thru "J" with 0 thru 9, and it's a one-time process, you could just run this query 10 times, replacing the character and the digit each time:
Update TableTb set [CostCenter]= replace([CostCenter], 'a', 0) where [CostCenter] is not null
Update TableTb set [CostCenter]= replace([CostCenter], 'b', 1) where [CostCenter] is not null
Alternatively, if you want to run the update query just once (and are VERY careful with your parenthesis and commas), you could do this:
Update TestTb set [CostCenter]= Replace(Replace(replace([C
Update TableTb set [CostCenter]= replace([CostCenter], 'a', 0) where [CostCenter] is not null
Update TableTb set [CostCenter]= replace([CostCenter], 'b', 1) where [CostCenter] is not null
Alternatively, if you want to run the update query just once (and are VERY careful with your parenthesis and commas), you could do this:
Update TestTb set [CostCenter]= Replace(Replace(replace([C
You really need a costcentre table
As said in : https://www.experts-exchange.com/questions/29125259/How-to-improve-performance-when-joining-on-text-field.html?anchorAnswerId=42728827#a42728827
And : https://www.experts-exchange.com/questions/29124895/Join-Tables-on-Calculated-Field.html?anchorAnswerId=42727244#a42727244
Albeit posted after the close, I would have hoped you saw the posts....
Just swapping 9 character costcenter fo 9 digit costcenter wont really change your join problems
Are you going to also change the data type of the column ? In which case you are touching all the tables with CostCenter anyway. So, do the correct conversion by building the costcentre table.
If you look at : https://www.experts-exchange.com/questions/29125259/How-to-improve-performance-when-joining-on-text-field.html?anchorAnswerId=42728827#a42728827 it does show a tblEmployeeCostCentres and a tblCostCentres.
Steps :
1) Build the tables
2) Populate CostCentres
3) Add a new column to the tables having alpha costcenters and add an INTERGER CostCentreID
4) Use the tblCostCentres as a lookup to use the encumbant alpha costcentre to retrieve and update the new CostCentreID
5) Test your joins etc.
6) Drop the old column from those tables in step 3.
7) Because CostCennterID is a Primary Key, your joins to get a CostCentre will be a lot faster.
Now, to this question, you have a single character replacement - so with alphabet of 26 characters (assuming convert to upper) then how is that not going to change the length ?
If A becomes 0,
B becomes 2
Then what does M,N,O,P etc become ? Because I know you have Q in there (5545Q7654; 4675Q2332; 9087Q2090 from your other questions)
If you want to pursue this replace alpha with number, then we can certainly come up with some code, but please, consider my comments above, and by all means dismiss the comments and let us know you want to pursue character replacement.
As said in : https://www.experts-exchange.com/questions/29125259/How-to-improve-performance-when-joining-on-text-field.html?anchorAnswerId=42728827#a42728827
And : https://www.experts-exchange.com/questions/29124895/Join-Tables-on-Calculated-Field.html?anchorAnswerId=42727244#a42727244
Albeit posted after the close, I would have hoped you saw the posts....
Just swapping 9 character costcenter fo 9 digit costcenter wont really change your join problems
Are you going to also change the data type of the column ? In which case you are touching all the tables with CostCenter anyway. So, do the correct conversion by building the costcentre table.
If you look at : https://www.experts-exchange.com/questions/29125259/How-to-improve-performance-when-joining-on-text-field.html?anchorAnswerId=42728827#a42728827 it does show a tblEmployeeCostCentres and a tblCostCentres.
Steps :
1) Build the tables
2) Populate CostCentres
3) Add a new column to the tables having alpha costcenters and add an INTERGER CostCentreID
4) Use the tblCostCentres as a lookup to use the encumbant alpha costcentre to retrieve and update the new CostCentreID
5) Test your joins etc.
6) Drop the old column from those tables in step 3.
7) Because CostCennterID is a Primary Key, your joins to get a CostCentre will be a lot faster.
Now, to this question, you have a single character replacement - so with alphabet of 26 characters (assuming convert to upper) then how is that not going to change the length ?
If A becomes 0,
B becomes 2
Then what does M,N,O,P etc become ? Because I know you have Q in there (5545Q7654; 4675Q2332; 9087Q2090 from your other questions)
If you want to pursue this replace alpha with number, then we can certainly come up with some code, but please, consider my comments above, and by all means dismiss the comments and let us know you want to pursue character replacement.
ASKER
Mark, first off thanks for all of your input.
For this exercise, which is to create a parallel CostCenterID field with a numeric data type in all tables that i use in joins.
1. Is this worthwhile to do? Its a total guess but I think joining numeric fields vs text will improve query performance by 30% or so.
2. How to do it? I was wondering if there's a dynamic method where you can create an AlphaToNumeric mapping table with two fields: Alpha would have the letters A-Z in 26 rows, and a Numeric field with the numbers 1-26. Would a Replace function accept field names so that finding A would replace with 1, etc? Short of that, I could create this CostCenterID field in the input filed in Excel before I upload them (and append subsequent ones) to Access.
3. I'm not concerned about the length of the data, as long as its uniform.
4. If a mapping table is the answer, I think that would work fine. It wouldn't be Dynamic, so if a new cost center appeared Id have to check for it and add it to my mapping table but if that's the way its done then I'd be happy to do that.
For this exercise, which is to create a parallel CostCenterID field with a numeric data type in all tables that i use in joins.
1. Is this worthwhile to do? Its a total guess but I think joining numeric fields vs text will improve query performance by 30% or so.
2. How to do it? I was wondering if there's a dynamic method where you can create an AlphaToNumeric mapping table with two fields: Alpha would have the letters A-Z in 26 rows, and a Numeric field with the numbers 1-26. Would a Replace function accept field names so that finding A would replace with 1, etc? Short of that, I could create this CostCenterID field in the input filed in Excel before I upload them (and append subsequent ones) to Access.
3. I'm not concerned about the length of the data, as long as its uniform.
4. If a mapping table is the answer, I think that would work fine. It wouldn't be Dynamic, so if a new cost center appeared Id have to check for it and add it to my mapping table but if that's the way its done then I'd be happy to do that.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
3. "Well, size does matter - especially with indexes...." I didn't know this but it makes sense. So on a table of 2,000 cost centers, an auto number index of 1-2000 would run much faster than an index of 2000 cost center ID's with 10-12 characters (numeric or alphanumeric). So I see the efficiency in this.
I'm also going to create an Index tables for my 1mil transactions. I'll use an make table query to create a two column table to associate transactions to cost centers with:
Transaction ID, Cost Center ID
Thanks for your time. If I get into more trouble I'll reach out :)
I'm also going to create an Index tables for my 1mil transactions. I'll use an make table query to create a two column table to associate transactions to cost centers with:
Transaction ID, Cost Center ID
Thanks for your time. If I get into more trouble I'll reach out :)
Sounds like a plan....
Also, try to normalise the Employee Cost Centers rather than being concatenated in a single field (column).
Have a read through : https://support.office.com/en-us/article/create-and-use-an-index-to-improve-performance-0a8e2aa6-735c-4c3a-9dda-38c6c4f1a0ce
But consider how you are acccessing the data. There is a risk of excessive / over indexing data and that becomes counter productive.
Have a close look at JOINs and WHERE clauses when considering indexes.
And we will be here to help....
Also, try to normalise the Employee Cost Centers rather than being concatenated in a single field (column).
Have a read through : https://support.office.com/en-us/article/create-and-use-an-index-to-improve-performance-0a8e2aa6-735c-4c3a-9dda-38c6c4f1a0ce
But consider how you are acccessing the data. There is a risk of excessive / over indexing data and that becomes counter productive.
Have a close look at JOINs and WHERE clauses when considering indexes.
And we will be here to help....
Will the replace function do what you want here?
https://www.techonthenet.com/access/functions/string/replace.php
HTH
David