Overpunch Character Conversion

[benchpolo]

The code below does not seem to work when the value is '0000004000' it is returning a value of 4.00 instead of 40.00. Can someone please advise how to convert '0000004000' to 40.00.

Thanks.

Code:

DECLARE @i int
    , @temp char(1), @tmp varchar(1), @a varchar(1)
    , @ebcdic char(2)
    , @result VARCHAR(255) , @result1 numeric(22,2) 
 
SET @i=1 
SET @result=''
set @a ='0'
 
WHILE (@i<=LEN(@s)) 
BEGIN
 
SET @temp=SUBSTRING(@s,@i,1) 
SET @tmp=SUBSTRING(@s,@i,1) 
 
SET @ebcdic=CASE @temp
 
--POSITIVE    
    WHEN '{' THEN '0'
    WHEN 'A' THEN '1'
    WHEN 'B' THEN '2'  
    WHEN 'C' THEN '3'  
    WHEN 'D' THEN '4'  
    WHEN 'E' THEN '5' 
    WHEN 'F' THEN '6'
    WHEN 'G' THEN '7'
    WHEN 'H' THEN '8'
    WHEN 'I' THEN '9'
 
--NEGATIVE
 
    WHEN '}' THEN '0' 
    WHEN 'J' THEN '1'
    WHEN 'K' THEN '2'  
    WHEN 'L' THEN '3'  
    WHEN 'M' THEN '4'  
    WHEN 'N' THEN '5' 
    WHEN 'O' THEN '6'
    WHEN 'P' THEN '7'
    WHEN 'Q' THEN '8'
    WHEN 'R' THEN '9'
    ELSE ' '
 
END
 
SET @a =CASE @tmp
--NEGATIVE
    WHEN '}' THEN '1' 
    WHEN 'J' THEN '1'
    WHEN 'K' THEN '1'  
    WHEN 'L' THEN '1'  
    WHEN 'M' THEN '1'  
    WHEN 'N' THEN '1' 
    WHEN 'O' THEN '1'
    WHEN 'P' THEN '1'
    WHEN 'Q' THEN '1'
    WHEN 'R' THEN '1'
    ELSE '0'
 
END
 
IF @ebcdic=''
 
SET @ebcdic=CASE
 
WHEN ASCII(@temp) BETWEEN 129 AND 137 THEN CHAR(ASCII(@temp)-32)   
WHEN ASCII(@temp) BETWEEN 145 AND 153 THEN CHAR(ASCII(@temp)-39)     
WHEN ASCII(@temp) BETWEEN 162 AND 169 THEN CHAR(ASCII(@temp)-47)    
WHEN ASCII(@temp) BETWEEN 193 AND 201 THEN CHAR(ASCII(@temp)-128)   
WHEN ASCII(@temp) BETWEEN 209 AND 217 THEN CHAR(ASCII(@temp)-135)
WHEN ASCII(@temp) BETWEEN 226 AND 233 THEN CHAR(ASCII(@temp)-143)  
WHEN ASCII(@temp) BETWEEN 240 AND 249 THEN CHAR(ASCII(@temp)-192)
 
ELSE ''
 
END
SET @result=@result+@ebcdic 
SET @i=@i+1 
END
 
set @result1 = convert(numeric,(case when @a ='1' then '-' else '+' end + substring( @s, 1,len(@s)-1) + ltrim(rtrim(@result)))) 
RETURN @result1/100
END

[ck9663]

What does your code trying to do? It would easier for us to recommend either a fix or a new technique.

Good Luck!!!


~~ CK

[benchpolo]

It is trying to convert an overpunch character on the data that we receive for positive or negative .. so if 000000400} is received the function will convert it to -40.00 bec. of "}" character. Our vendor somehow decided to provide overpunch characters on the negative value and not on the zero positive value.

Sure, I'm always open for new suggestion.
Thanks

[ck9663]

Is the "}" character fixed for negative values? Are there any other character used? If it's positive, do they put in any other character identifier? Could you post some sample (maybe 10?) rows of positive and negative numbers and how it will be converted to?


~~ CK

[benchpolo]

It is a total of 10 length and the last digit is always and overpunch..

000000400}
000000400{
000000500A
000000500J
000000600C
000000600L
000000310E
000000311N
000000222I
000000222R

Thanks.

[benchpolo]

but when the value is not an overpunch such as 0000004000 it should be as is like 40.00

[ck9663]

So, based on the sample you posted, what would be the equivalent converted value? Are those all positives and only the first one as negative?


~~ CK

[benchpolo]

THe negatives are okay because they are being converted throught the posted sql function script. It is when it encounters a zero "0" if its zero then it should take the as is value since the vendor does not provide the "{" overpunch value. So if the value of the field has an overpunch character at the end then convert it accordingly else take the value as is.. make sense?
Thanks ck!

[ck9663]

It looks to me like a straight-forward string manipulation. If I understand it right, you don't need a character by character parsing, just parse the rightmost. Try this script. It is based on your sample data.

 
select
yourvalue, 
parsed =
      substring(yourvalue, 1, len(yourvalue)-1) + 
      case
         when right(yourvalue,1) in ('{','}','0') then '0'         
         when right(yourvalue,1) between 'A' and 'I' then cast(ascii(right(yourvalue,1))-64 as char(1))
         when right(yourvalue,1) between 'J' and 'R' then cast(ascii(right(yourvalue,1))-73 as char(1))
         else ''
      end,
factor =  
      case
         when right(yourvalue,1) in ('{','0') or right(yourvalue,1) between 'A' and 'I' then 1
         when right(yourvalue,1) in ('}') or right(yourvalue,1) between 'J' and 'R' then -1
      end,
converted = 
   (cast(
         substring(yourvalue, 1, len(yourvalue)-1) + 
         case
            when right(yourvalue,1) in ('{','}','0') then '0'         
            when right(yourvalue,1) between 'A' and 'I' then cast(ascii(right(yourvalue,1))-64 as char(1))
            when right(yourvalue,1) between 'J' and 'R' then cast(ascii(right(yourvalue,1))-73 as char(1))
            else ''
         end
        as int) *
      case
         when right(yourvalue,1) in ('{','0') or right(yourvalue,1) between 'A' and 'I' then 1
         when right(yourvalue,1) in ('}') or right(yourvalue,1) between 'J' and 'R' then -1
      end) / 100.00    
from
(
   select yourvalue = '000000400}' 
   union all
   select '000000400{'
   union all
   select '000000500A'
   union all
   select '000000500J'
   union all
   select '000000600C'
   union all
   select '000000600L'
   union all
   select '000000310E'
   union all
   select '000000311N'
   union all
   select '000000222I'
   union all
   select '000000222R'
   union all
   select '0000004000'
) yourdata
 

The "union all" sub-query was created just to create a sample data set. The four columns will show the value based on your sample data; the parsed value with the overpunch character converted, just so you can check if the letter-to-number conversion is correct; the sign converter (positive or negative); and the converted-to-integer value...

If you're going to convert this into a function, the only portion you need is the formula for the "converted" column.


Good Luck!!!

~~ CK