I have character data stored in a column that was imported from a data file. The character data represents an integer value, but.. the last (rightmost) character isn't always a digit character. I'm attempting to convert the character data into the integer value using a SQL expression, but it's not working.
My attempt at a SQL statement is shown below, and a test case that demonstrates it's not working. My approach is to split off the rightmost character from the string, do the appropriate conversion, and then string it back together and cast to integer.
Q: How can I fix my SQL expression to convert this correctly, or what SQL expression can be used to do the conversion?
DETAILS
The rightmost character in the string can be one of the values in the "Code" column below. The "Digit" column shows that actual integer value represented by the character, and the "Sign" column shows whether the overall string is to be interpreted as a negative value, or a positive value.
For example, the string value '023N' represents an integer value of +235. (The rightmost 'N' character represents a digit value of 5, with a positive sign). The string value of '104}' represents an integer value of -1040. (The rightmost '}' charcacter represents a digit value of '0' and makes the overall integer value negative.)
Here's the table that shows the 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 a table of example values:
Create Table #Punch
(
aa varchar(20)
)
Insert Into #Punch values ('046')
Insert into #Punch values ('027')
Insert into #Punch values ('004')
Insert into #Punch values ('020')
Insert into #Punch values ('090')
And this is the SQL statement do do the conversion, but it's not working correctly for character strings that have just regular digit characters. (The sample table above are examples of other character strings that should be converted to integer value.
This SQL statement is returning an integer value of 184 for the character string 046, when I expect it to return 46.
Q: Why is my SQL statement returning an integer value of 184 instead of 46 for the character string '046'?
select
aa, Answervalue =
(cast(
substring(aa, 1, len(aa)-1) +
case
when right(aa,1) in ('{','}','0') then '0'
when right(aa,1) between 'A' and 'I' then cast(ascii(right(aa,1))-64 as char(1))
when right(aa,1) between 'J' and 'R' then cast(ascii(right(aa,1))-73 as char(1))
else ''
end
as int) *
case
when right(aa,1) in ('{','0') or right(aa,1) between 'A' and 'I' then 1
when right(aa,1) in ('}') or right(aa,1) between 'J' and 'R' then -1
when aa in (aa) then aa
end)
from
(
select aa from #Punch
) bb
For given inserted value, the result of "046" is coming as "184". It should be "46". For "004" the result is coming as "0". It should be "4". Other than these issue, logic works fine. If the column value aa is numeric, and there is no code\characters (for example {,A,N,B, etc) in the value, I want to put as original value. so if it is 046 then value should be 46.
Thank you in advance !
I couldn't edit my original (I missed your point about the numeric) so here it is again:
It looks to me like you inserted the wrong data into your table.
Try:
Insert Into #Punch values ('04O')
Insert into #Punch values ('02P')
Insert into #Punch values ('00D')
Insert into #Punch values ('02{')
Insert into #Punch values ('09}')
As far as checking to see if the value is numeric, that's another issue. Try using:
select
aa, Answervalue = CASE WHEN IsNumeric(aa) = 1 THEN aa ELSE
(cast(
substring(aa, 1, len(aa)-1) +
case
when right(aa,1) in ('{','}','0') then '0'
when right(aa,1) between 'A' and 'I' then cast(ascii(right(aa,1))-64 as char(1))
when right(aa,1) between 'J' and 'R' then cast(ascii(right(aa,1))-73 as char(1))
else ''
end
as int) *
case
when right(aa,1) in ('{','0') or right(aa,1) between 'A' and 'I' then 1
when right(aa,1) in ('}') or right(aa,1) between 'J' and 'R' then -1
when aa in (aa) then aa
end) END
from
(
select aa from #Punch
) bb