Char to Integer conversion in SQL

[rudra]

Hello Guys,

I have a character field of 15 length, but the data in this is like Decimal 13, 3.

Ex: 200000.000- <- it?s a character filed but data is in Dec format, pls. see the ??? value at the end.

Now I need to convert this to a negative Integer, any Idea ??????

Int(200000.000-) is giving me junk ++++++++++++++.

Thanks!! in advance.

[K2r400]

Re: Char to Integer conversion in SQL

with t1 as (Select '  200000.000-  ' z1 from sysibm/sysdummy1)
Select                                                        
case posstr(z1, '-')                                          
  when 0 then int(trim(z1))                                   
  else   int(replace(trim(z1), '-', '')) * -1                 
end z1                                                        
from t1  

....+....1....                     
           Z1                      
      200.000-                     
********  Fin de données  ********

[kitvb1]

Re: Char to Integer conversion in SQL

 
select 
(case
when substr('2000.000-', 9, 1) = '-' 
then (cast(substr('2000.000-', 1, 8) as dec(7, 3)) * -1 ) 
else (cast(substr('2000.000-', 1, 8) as dec(7, 3)) * 1 ) 
end)
from aulkkwmods/oepda

[B.Hauser]

Re: Char to Integer conversion in SQL

If you want to make sure that if a - sign is passed it is on the first or last position, you may also use the following SQL Statement:

Select Case When Left(Trim(MyFld), 1) = '-' or Right(Trim(MyFld), 1) = '-'
            Then - Int(Trim('-' from Trim(MyFld)) else Int(MyFld) End
From MyTable 

Birgitta

[K2r400]

Re: Char to Integer conversion in SQL

Birgitta,

Dow you know why the Right function give me a bad result in a Case condition ?
ex :

with t1 as (Select cast('  200000.000-  ' as char(15)) z0     
from sysibm/sysdummy1)                                        
Select                                                        
case posstr(trim(z0), '-') when 0                             
  then int(trim(z0))                                          
  else   int(Strip(trim(z0), B, '-')) * -1                    
  end z1,                                                     
  Right(Trim(z0), 1),                                         
case When Left(Trim(z0), 1) = '-' or Right(Trim(z0), 1) = '-' 
  then int(trim(z0))                                          
  else   int(Strip(trim(z0), B, '-')) * -1                    
  end z2                                                      
from t1

Result :

....+....1....+....2....+....3....+....4....+.. 
           Z1   RIGHT                       Z2  
      200,000-  -                ++++++++++++++ 
********  Fin de données  ********

[rudra]

Re: Char to Integer conversion in SQL

Thank!! you all for your replies.