Announcement

Collapse
No announcement yet.

Casting character to number

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Casting character to number

    Hi, I have a flat file with a field like this:

    'AAAABBBBCC0000123456700DDDFFF'

    the number 0000123456700 is a decimal(13, 5) but in this flat file there's no decimal separator.

    I try with a cast but it doesn't work.

    Any idea's?

    Many thanks.

  • #2
    You can't cast direct to the fractional decimal (to my knowledge). If there is no decimal point it will be interpreted as a whole number., so 0000123456700 would become 0000123456700.0. Which might not fit inside a 13,5 as there are only 8 whole number digits anyway.

    I can think of two methods, both in the example SQL below.
    Method 1, use substring to separate the integer and decimal components, and use string manipulation to insert the decimal point in between. Then cast the result to decimal 13,5.
    Method 2, use substring to extract the number, and case it to decimal 13,0 (do not use int, as we want to do an accurate division on this). Then divide it by 100000 to shift the decimal to the correct place. The division result will be decimal 31,18 but the SQL engine will be smart enough to cast from that result to the 13,5 column/variable you are inserting the value in to.

    I don't know which method is quicker for the SQL engine to perform.

    Code:
    with testData (field1) as (
      values('AAAABBBBCC0000123456700DDDFFF')
    )
    select
      decimal(
        substring(field1,11,8) || '.' || substring(field1,19,5)
      ,13,5
     ) as method1,
     decimal(
      substring(field1,11,13)
      ,13,0
     ) / 100000 as method2,
     t.*
    from testData t;

    Comment


    • #3
      Hi vector,
      thanks for yuor reply. It works!

      Comment


      • #4
        Hi vector,
        thanks for yuor reply. It works!

        Comment

        Working...
        X