change alpha to numeric

[Rick]

hi all,
once again i find myself in need of the expert advice i have always found
here.
i have a series of alpha/numeric values in a table column like this:

3A1111
3A1112
3A1113
3A1114
3A1115
....
3Z9999

what i need to do is to change the alpha letters to a numeric value, such as:

A to 10 for 3101111
B to 11 for 3111112
C to 12 for 3121113
D to 13 for 3131114
....
thru
Z to 36 for 336....

can some kind person point me in the right direction to accomplish this.

thanks a lot!

 

[fredg]

hi all,
once again i find myself in need of the expert advice i have always found
here.
i have a series of alpha/numeric values in a table column like this:

3A1111
3A1112
3A1113
3A1114
3A1115
...
3Z9999

what i need to do is to change the alpha letters to a numeric value, such as:

A to 10 for 3101111
B to 11 for 3111112
C to 12 for 3121113
D to 13 for 3131114
...
thru
Z to 36 for 336....

can some kind person point me in the right direction to accomplish this.

thanks a lot!

How about 35 for the letter Z?

As long as it is a Capital letter value....

NewValue: Left([OldValue],1) & Asc(Mid([OldValue],2,1))-55 &
Mid([OldValue],3)

 

[Carl Rapson]

hi all,
once again i find myself in need of the expert advice i have always found
here.
i have a series of alpha/numeric values in a table column like this:

3A1111
3A1112
3A1113
3A1114
3A1115
...
3Z9999

what i need to do is to change the alpha letters to a numeric value, such
as:

A to 10 for 3101111
B to 11 for 3111112
C to 12 for 3121113
D to 13 for 3131114
...
thru
Z to 36 for 336....

can some kind person point me in the right direction to accomplish this.

thanks a lot!

Try this:

UPDATE

SET [field]=Left([field],1) &
str(Asc(Mid([field],2,1))-55) & Right([field],4);

Carl Rapson

 

[KARL DEWEY]

Build a table named tblConversion with two fields, Alpha and Numeric.

This will work IF there is only one alpha character and it is always in
the second position.
SELECT Alpha_Numeric.Alpha_Numeric,
Replace([Alpha_Numeric],[Alpha],[Number]) AS Translated
FROM tblConversion, Alpha_Numeric
WHERE (((Right(Left([Alpha_Numeric],2),1))=[Alpha]));

If the alpha character is in more than the second position then you need
more criteria like this --
SELECT Alpha_Numeric.Alpha_Numeric,
Replace([Alpha_Numeric],[Alpha],[Number]) AS Translated
FROM tblConversion, Alpha_Numeric
WHERE (((Right(Left([Alpha_Numeric],2),1))=[Alpha])) OR
(((Right(Left([Alpha_Numeric],3),1))=[Alpha]));

 

[Marshall Barton]

once again i find myself in need of the expert advice i have always found
here.
i have a series of alpha/numeric values in a table column like this:

3A1111
3A1112
3A1113
3A1114
3A1115
...
3Z9999

what i need to do is to change the alpha letters to a numeric value, such as:

A to 10 for 3101111
B to 11 for 3111112
C to 12 for 3121113
D to 13 for 3131114
...
thru
Z to 36 for 336....

Kind of a nasty thing to do. I hope it is a one time
change. Actually, it would be better if you followed the
first rule of normalization and put thar data in 3 separate
fields.

I guess I would suggest this kind of expresion:

SELECT Left(field,1) & (Asc(Mid(field,2,1)) - Asc("A") + 10)
& Mid(field,3) As newfield
FROM table
WHERE Mid(field,2,1) Like "[A-Z]"

(Note that Z will be 35, not 36)

 

[MGFoster]

Build a table named tblConversion with two fields, Alpha and Numeric.

This will work IF there is only one alpha character and it is always in
the second position.
SELECT Alpha_Numeric.Alpha_Numeric,
Replace([Alpha_Numeric],[Alpha],[Number]) AS Translated
FROM tblConversion, Alpha_Numeric
WHERE (((Right(Left([Alpha_Numeric],2),1))=[Alpha]));

If the alpha character is in more than the second position then you need
more criteria like this --
SELECT Alpha_Numeric.Alpha_Numeric,
Replace([Alpha_Numeric],[Alpha],[Number]) AS Translated
FROM tblConversion, Alpha_Numeric
WHERE (((Right(Left([Alpha_Numeric],2),1))=[Alpha])) OR
(((Right(Left([Alpha_Numeric],3),1))=[Alpha]));

I believe I'd use the MID() expression rather than the Right(Left(
stuff, easier to read/maintain:

WHERE Mid$(Alpha_Numeric,2,1) = Alpha

 

[Jamie Collins]

Build a table named tblConversion with two fields, Alpha and Numeric.

A data-driven approach -- I like it! Make me an MVP and I'll mark this as an
answer

Jamie.

--

 

[Jamie Collins]

it would be better if you followed the
first rule of normalization and put thar data in 3 separate
fields.

I think you've misunderstood the rule: '3101111' is a scalar value. You are
suggesting splitting into sub atomic values, which is not unreasonable given
the OP's usage but failure to do so does not AFAIK constitute
denormalization. Do you split ISBN values into separate columns for each of
its subatomic elements (group, publisher, item, checksum)? Would your censure
Amazon for not doing so?

Jamie.

--

 

[Marshall Barton]

I think you've misunderstood the rule: '3101111' is a scalar value. You are
suggesting splitting into sub atomic values, which is not unreasonable given
the OP's usage but failure to do so does not AFAIK constitute
denormalization. Do you split ISBN values into separate columns for each of
its subatomic elements (group, publisher, item, checksum)? Would your censure
Amazon for not doing so?

IMO, usage defines the atoms.

 

[Klatuu]

Well, we could put each character or digit in a separate column. Then we
could concatenate them any way we wanted. We could build a cross reference
table that would show which contatenations would translate to what useable
values.

One thing I haven't seen in this post is whether the alpha characters will
always be upper case. If not, you might get some unusual results. I would
suggest

NewValue: Left([OldValue],1) & Asc(UCase(Mid([OldValue],2,1)))-55 &
Mid([OldValue],3)

 

[Klatuu]

Sorry, Marsh, I couldn't help myself. I do agree with you. It is all about
the data supporting the business rules.

 

[Rick]

hi all,
once again i find myself in need of the expert advice i have always found
here.
i have a series of alpha/numeric values in a table column like this:

3A1111
3A1112
3A1113
3A1114
3A1115
...
3Z9999

what i need to do is to change the alpha letters to a numeric value, such as:

A to 10 for 3101111
B to 11 for 3111112
C to 12 for 3121113
D to 13 for 3131114
...
thru
Z to 36 for 336....

can some kind person point me in the right direction to accomplish this.

thanks a lot!

more info which may help determine a solution. i have imported a data table
from an accounting application that has many colums. the first column
contains a value such as 3a1234 which is a reference number that refers to an
invoice number in the same table.
the reference numbers are unique alpha numeric values.

in the table imported, they range from 300000 thru 399999, from 3A0000 thru
3Z9999, from 3a0000 thru 3z9999. the second position can be alpha or numeric
and if alpha, can be lower or upper case.
i need to import values from this table into another database that will only
allow numeric values in the reference field.

i tried karl's method and that semi worked. it did change the values when i
ran the query, but did not change the values in the table that i needed them
changed in.

i have appened the data from the imported table to a new table named
omd_order_header. this is the table that has the column named OHeader_id that
contains the reference values. i need to update the values in this column in
this table.

Karl's method created a select query, but did not update the OHeader_ID
field in the omd_order_header table. here is the code i used:

SELECT omd_order_header.OHeader_ID,
Replace([omd_order_header.OHeader_ID],[Alpha],[Numeric]) AS Translated
FROM omd_order_header, conversion_alpha_to_numeric
WHERE (((Right(Left([omd_order_header.OHeader_ID],2),1))=[Alpha]));

this creates a 2 column table with OHeader_ID and Translated. the Translated
column contains the updated reference values but nothing is changed in the
OHeader_ID column in the omd_order_header table.

hope this helps. i really need to figure this out. i have approximately 197k
records with the reference number that need to changed.

thanks again

 

[KARL DEWEY]

Your SQL has a mixture of your field names and mine.

All you need to do is make the query a 'Make Table' query.

Copy your table for safety and run this query ---
UPDATE omd_order_header, conversion_alpha_to_numeric SET
omd_order_header.OHeader_ID =
Replace([omd_order_header].[OHeader_ID],[conversion_alpha_to_numeric].[Alpha],[conversion_alpha_to_numeric].[Numeric])
WHERE (((Right(Left([omd_order_header].[OHeader_ID],2),1))=[Alpha]));

--
KARL DEWEY
Build a little - Test a little

hi all,
once again i find myself in need of the expert advice i have always found
here.
i have a series of alpha/numeric values in a table column like this:

3A1111
3A1112
3A1113
3A1114
3A1115
...
3Z9999

what i need to do is to change the alpha letters to a numeric value, such as:

A to 10 for 3101111
B to 11 for 3111112
C to 12 for 3121113
D to 13 for 3131114
...
thru
Z to 36 for 336....

can some kind person point me in the right direction to accomplish this.

thanks a lot!

more info which may help determine a solution. i have imported a data table
from an accounting application that has many colums. the first column
contains a value such as 3a1234 which is a reference number that refers to an
invoice number in the same table.
the reference numbers are unique alpha numeric values.

in the table imported, they range from 300000 thru 399999, from 3A0000 thru
3Z9999, from 3a0000 thru 3z9999. the second position can be alpha or numeric
and if alpha, can be lower or upper case.
i need to import values from this table into another database that will only
allow numeric values in the reference field.

i tried karl's method and that semi worked. it did change the values when i
ran the query, but did not change the values in the table that i needed them
changed in.

i have appened the data from the imported table to a new table named
omd_order_header. this is the table that has the column named OHeader_id that
contains the reference values. i need to update the values in this column in
this table.

Karl's method created a select query, but did not update the OHeader_ID
field in the omd_order_header table. here is the code i used:

SELECT omd_order_header.OHeader_ID,
Replace([omd_order_header.OHeader_ID],[Alpha],[Numeric]) AS Translated
FROM omd_order_header, conversion_alpha_to_numeric
WHERE (((Right(Left([omd_order_header.OHeader_ID],2),1))=[Alpha]));

this creates a 2 column table with OHeader_ID and Translated. the Translated
column contains the updated reference values but nothing is changed in the
OHeader_ID column in the omd_order_header table.

hope this helps. i really need to figure this out. i have approximately 197k
records with the reference number that need to changed.

thanks again