How to convert Signed Overpunch Characters to numeric value in SQL 2005

[benchpolo]

I am trying to convert a signed overpunch characters to a readable numeric value in SQL 2005. Unfortunately, I am not able to figure out the logic on how to convert it. I have never encountered this type of data before. For example,

10} is -100
45A is 451

Can someone assist me on how to write the SQL logic to convert this. I'm stuck. Your help is appreciated. Thanks.

[NeoPa]

BenchPolo:
signed overpunch characters

What are these? Are they something you would expect everyone to know about? I certainly have no idea where this is coming from.

[Jerry Winston]

Do you already have the values in a table stored as character data or the like?


@NeoPa
I haven't had to work with them much but
I think this is the set of characters for signed overpunch characters:

{ = 0
A = 1
B = 2
C = 3
D = 4
E = 5
F = 6
G = 7
H = 8
I = 9
} = -0
J = -1
K = -2
L = -3
M = -4
N = -5
O = -6
P = -7
Q = -8
R = -9

[NeoPa]

Thanks Jerry.

Can I ask why only the last digit is indicated by an SOC?

Will it always be only the units that are designated that way?

[benchpolo]

This is an old technology, and one of our vendor is passing this value to us (see reference below)

Code Digit Sign
---- ----------- ----
} 0 -
J 1 -
K 2 -
L 3 -
M 4 -
N 5 -
O 6 -
P 7 -
Q 8 -
R 9 -
{ 0 +
A 1 +
B 2 +
C 3 +
D 4 +
E 5 +
F 6 +
G 7 +
H 8 +
I 9 +

I was able to write a logic surrounding the conversion of the signed overpunch characters

case when right(total_amount_paid_by_all_sources,1) = '}' then '-' + left(total_amount_paid_by_all_sources,7) + '0'
when right(total_amount_paid_by_all_sources,1) = 'J' then '-' + left(total_amount_paid_by_all_sources,7) + '1'
when right(total_amount_paid_by_all_sources,1) = 'K' then '-' + left(total_amount_paid_by_all_sources,7) + '2'
when right(total_amount_paid_by_all_sources,1) = 'L' then '-' + left(total_amount_paid_by_all_sources,7) + '3'
when right(total_amount_paid_by_all_sources,1) = 'M' then '-' + left(total_amount_paid_by_all_sources,7) + '4'
when right(total_amount_paid_by_all_sources,1) = 'N' then '-' + left(total_amount_paid_by_all_sources,7) + '5'
when right(total_amount_paid_by_all_sources,1) = 'O' then '-' + left(total_amount_paid_by_all_sources,7) + '6'
when right(total_amount_paid_by_all_sources,1) = 'P' then '-' + left(total_amount_paid_by_all_sources,7) + '7'
when right(total_amount_paid_by_all_sources,1) = 'Q' then '-' + left(total_amount_paid_by_all_sources,7) + '8'
when right(total_amount_paid_by_all_sources,1) = 'R' then '-' + left(total_amount_paid_by_all_sources,7) + '9'
when right(total_amount_paid_by_all_sources,1) = '{' then left(total_amount_paid_by_all_sources,7) + '0'
when right(total_amount_paid_by_all_sources,1) = 'A' then left(total_amount_paid_by_all_sources,7) + '1'
when right(total_amount_paid_by_all_sources,1) = 'B' then left(total_amount_paid_by_all_sources,7) + '2'
when right(total_amount_paid_by_all_sources,1) = 'C' then left(total_amount_paid_by_all_sources,7) + '3'
when right(total_amount_paid_by_all_sources,1) = 'D' then left(total_amount_paid_by_all_sources,7) + '4'
when right(total_amount_paid_by_all_sources,1) = 'E' then left(total_amount_paid_by_all_sources,7) + '5'
when right(total_amount_paid_by_all_sources,1) = 'F' then left(total_amount_paid_by_all_sources,7) + '6'
when right(total_amount_paid_by_all_sources,1) = 'G' then left(total_amount_paid_by_all_sources,7) + '7'
when right(total_amount_paid_by_all_sources,1) = 'H' then left(total_amount_paid_by_all_sources,7) + '8'
when right(total_amount_paid_by_all_sources,1) = 'I' then left(total_amount_paid_by_all_sources,7) + '9'
end as convert1

The dilemma that i'm having now is converting the value to a 2 decimal places (see below)

Signed overpunch 0002458{ converted value 24580.0. It should be 245.80. Can you help? Thanks.

[NeoPa]

Something built around this then :

SELECT @Var = CAST(LEFT([Field], LEN([Field]) - 1)) * 10 + 
              IF(RIGHT([Field],1) IN('{','}'), 
                 0,
                 IF(RIGHT([Field], 1)<'J',
                    ASCII(RIGHT([Field], 1)) - ASCII('@'),
                    ASCII('I') - ASCII(RIGHT([Field], 1))))

[NeoPa]

I've just caught your latest post (#5). The change should be pretty trivial :
Something built around this then :

SELECT @Var = (CAST(LEFT([Field], LEN([Field]) - 1)) * 10 + 
              IF(RIGHT([Field],1) IN('{','}'), 
                 0,
                 IF(RIGHT([Field], 1)<'J',
                    ASCII(RIGHT([Field], 1)) - ASCII('@'),
                    ASCII('I') - ASCII(RIGHT([Field], 1))))) / 100

[benchpolo]

I already converted the signed overpunch characters to its corresponding numeric value. I need assistance though in converting it to 2 decimal places.

from 24580.0 It should be 245.80. Can you help? Thanks.

[NeoPa]

Well, (a bit confused) what I did was divide the result by 100. If you find that complicated with your code then perhaps your code could do with some looking at. Alternatively, you could simply use the 6 lines of code I posted to do the whole job. Your choice of course.

[Jerry Winston]

Can we just cheat and multiply by .01?:

SELECT 24580.0 * .01

I apologize for this shortcut ahead of time. My mental CPU is tapped trying to solve another problem.O_o

[benchpolo]

multiplying to .01 works.. cheating is good for as long as I deliver the correct result lol.

[NeoPa]

Well, I'm glad that multiplying by 0.01 worked. Shame dividing by 100 doesn't seem to have done the job ;)

[smoktan]

Thanks for this code.. It helped.. me