0

I have looked at:

I have a column that is six long and a character column. A typical value would be AA01AA. I need to substring the middle 2 characters out of the value and convert to a number.

I am doing this with the following code: TRIM(L '0' FROM(SUBSTRING(Myfield, 3, 2))). In the example value above that gives me 1. The problem comes in when the value is 000000. The trim returns ''. I need it to return 0.

I have tried REPLACE(TRIM(L '0' FROM(SUBSTRING(Myfield, 3, 2))),'' ,'0') but that simply gives me a blank string back. I have also tried TRANSLATE(TRIM(L '0' FROM(SUBSTRING(Myfield, 3, 2))), '0', '') but that gives an error about parameter 03 being an invalid data type, length etc.

I would appreciate any help.

Community
  • 1
  • 1
pkidza
  • 449
  • 1
  • 4
  • 12

1 Answers1

2

Something like this should do the trick:

integer(substr(myField,3,2)))

The SUBSTR extracts the two characters, the INTEGER takes it as input and converts it to a number. TRIM is not necessary at all.

values(integer(substr('000000',3,2)))

1          
-----------
          0

  1 record(s) selected.
data_henrik
  • 16,724
  • 2
  • 28
  • 49