sql server - SQL Logic doesn't work -

i have character data stored in column imported data file. character data represents integer value, but.. last (rightmost) character isn't digit character. i'm attempting convert character data integer value using sql expression, it's not working.

my attempt @ sql statement shown below, , test case demonstrates it's not working. approach split off rightmost character string, appropriate conversion, , string , cast integer.

q: how can fix sql expression convert correctly, or sql expression can used conversion?

details

the rightmost character in string can 1 of values in "code" column below. "digit" column shows actual integer value represented character, , "sign" column shows whether overall string interpreted negative value, or positive value.

for example, string value '023n' represents integer value of +235. (the rightmost 'n' character represents digit value of 5, positive sign). string value of '104}' represents integer value of -1040. (the rightmost '}' charcacter represents digit value of '0' , makes overall integer value negative.)

here's table shows required conversion.

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'    + 

here's table of example values:

 create table #punch  (  aa varchar(20)  )  insert #punch values ('046') insert #punch values ('027') insert #punch values ('004') insert #punch values ('020') insert #punch values ('090') 

and sql statement do conversion, it's not working correctly character strings have regular digit characters. (the sample table above examples of other character strings should converted integer value.

this sql statement returning integer value of 184 character string 046, when expect return 46.

q: why sql statement returning integer value of 184 instead of 46 character string '046'?

      select       aa, answervalue =       (cast(      substring(aa, 1, len(aa)-1) +      case         when right(aa,1) in ('{','}','0') '0'                  when right(aa,1) between 'a' , 'i' cast(ascii(right(aa,1))-64 char(1))         when right(aa,1) between 'j' , 'r' cast(ascii(right(aa,1))-73 char(1))         else ''      end     int) *   case      when right(aa,1) in ('{','0') or right(aa,1) between 'a' , 'i' 1      when right(aa,1) in ('}') or right(aa,1) between 'j' , 'r' -1      when aa in (aa) aa    end)   ( select aa #punch  ) bb 

---

for given inserted value, result of "046" coming "184". should "46". "004" result coming "0". should "4". other these issue, logic works fine. if column value aa numeric, , there no code\characters (for example {,a,n,b, etc) in value, want put original value. if 046 value should 46.

thank in advance !

i couldn't edit original (i missed point numeric) here again:

it looks me inserted wrong data table.

try:

insert #punch values ('04o') insert #punch values ('02p') insert #punch values ('00d') insert #punch values ('02{') insert #punch values ('09}') 

as far checking see if value numeric, that's issue. try using:

      select       aa, answervalue = case when isnumeric(aa) = 1 aa else       (cast(      substring(aa, 1, len(aa)-1) +      case         when right(aa,1) in ('{','}','0') '0'                  when right(aa,1) between 'a' , 'i' cast(ascii(right(aa,1))-64 char(1))         when right(aa,1) between 'j' , 'r' cast(ascii(right(aa,1))-73 char(1))         else ''      end     int) *   case      when right(aa,1) in ('{','0') or right(aa,1) between 'a' , 'i' 1      when right(aa,1) in ('}') or right(aa,1) between 'j' , 'r' -1      when aa in (aa) aa    end) end   ( select aa #punch  ) bb