February 29, 2012 at 1:14 pm
Hi -
Below is an algorithm used in excel for converting an alpha numeric code into a number. Need help replicating the same in SQL.
=IF(C2="","0",SUMPRODUCT( POWER(36,LEN(C2) -ROW(INDIRECT("1:"&LEN(C2)))),(CODE(UPPER(MID(C2,ROW(INDIRECT("1:"&LEN(C2))),1))) -48*(CODE(MID(C2,ROW(INDIRECT("1:"&LEN(C2))),1))<58) -55*(CODE(MID(C2,ROW(INDIRECT("1:"&LEN(C2))),1))>64))))
Any Help is much appreciated.
February 29, 2012 at 1:45 pm
I have a better idea.. Why don't you tell us what it is supposed to do and we'll talk about solutions..
CEWII
February 29, 2012 at 1:51 pm
Well. In a row value of alpha numeric code like 'E126W4SD', the above excel algorithm will provide values replacing the alphabets, based on alphabet series. Lets say an alphabet "A" starts with a value 1 and "Z" ends with a value 26.
February 29, 2012 at 1:55 pm
This formula is jammed with functions—ROW() and INDIRECT()—that depend on the underlying structure of Excel that consists of a grid of cells with each cell having a specific location within that grid.
The underlying structure of SQL is completely different and these particular functions are meaningless in that structure. SQL consists of an unordered set of records. A record has no particular ordering with respect to any other record; a field (attribute) has no particular ordering with respect to any other field; and a record has no specific location within that set.
Furthermore, we have no way of knowing whether the cells referenced in the Excel formula contain their own formulas. There is simply no way to "translate" this formula into SQL. You need to start over from scratch.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 29, 2012 at 2:06 pm
subahan_syed (2/29/2012)
Well. In a row value of alpha numeric code like 'E126W4SD', the above excel algorithm will provide values replacing the alphabets, based on alphabet series. Lets say an alphabet "A" starts with a value 1 and "Z" ends with a value 26.
so does E126W4SD = 1099407588925 ?
cos that what my version of excel displays....so, now, what are the "conversion rules" ?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 29, 2012 at 2:09 pm
that would be 5126234194
February 29, 2012 at 2:31 pm
so does E126W4SD = 1099407588925 ?
cos that what my version of excel displays....so, now, what are the "conversion rules" ?
subahan_syed (2/29/2012)
that would be 5126234194
...hmm..seems my version excel isnt working 😀
so E126W4SD should equal 5126234194
....based on what you have just posted above, is this what you are after
'E'...convert to 5 (fifth letter of alphabet) PLUS
'126'...ignore cos "is numeric" PLUS
'W' ..convert to 23 (23rd letter of alphabet) PLUS
etc
ie....convert letters to number based on position number in alphabet and leave "numerals" as is ?
is this what you want?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 1, 2012 at 1:10 am
My bad you were right. It should be 5126234194.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply