Overpunch Character Conversion

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • benchpolo
    New Member
    • Sep 2007
    • 142

    #1

    Overpunch Character Conversion

    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:

    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
    Last edited by Niheel; Jun 9 '11, 05:41 PM. Reason: It's much easier to understand if you post the explanation first then the code.
  • ck9663
    Recognized Expert Specialist
    • Jun 2007
    • 2878

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

    Good Luck!!!


    ~~ CK

    Comment

    • benchpolo
      New Member
      • Sep 2007
      • 142

      #3
      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

      Comment

      • ck9663
        Recognized Expert Specialist
        • Jun 2007
        • 2878

        #4
        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

        Comment

        • benchpolo
          New Member
          • Sep 2007
          • 142

          #5
          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.

          Comment

          • benchpolo
            New Member
            • Sep 2007
            • 142

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

            Comment

            • ck9663
              Recognized Expert Specialist
              • Jun 2007
              • 2878

              #7
              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

              Comment

              • benchpolo
                New Member
                • Sep 2007
                • 142

                #8
                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!

                Comment

                • ck9663
                  Recognized Expert Specialist
                  • Jun 2007
                  • 2878

                  #9
                  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.

                  Code:
                  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

                  Comment

                  Working...