CODEWITHSUNDEEP
sqldb2
pkidza
pkidza

Reputation: 459

DB2 TRIM 000000 to 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.

Upvotes: 0

Views: 286

Answers (1)

data_henrik
data_henrik

Reputation: 17118

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.

Upvotes: 2

Related Questions