DB2 coalesce function return null on substring-CodePudding

Running into a problem using DB2 on iSeries V7R4.

Would like to split a varchar into 2 variables (in this examples the intos are missing, but doesn't matter).

select coalesce(null, 'DATA') from SYSIBM.SYSDUMMY1; => DATA as expected.

SELECT
    SUBSTR('ABC.DEF', 1, LOCATE('.','ABC.DEF')-1) as FIRST_PART
  , SUBSTR('ABC.DEF', LOCATE('.','ABC.DEF') 1)   as SECOND_PART
FROM SYSIBM.SYSDUMMY1;

=> {ABC, DEF} as expected.

SELECT
    SUBSTR('ABCDEF', 1, LOCATE('.','ABCDEF')-1) as FIRST_PART
  , SUBSTR('ABCDEF', LOCATE('.','ABCDEF') 1)   as SECOND_PART
FROM SYSIBM.SYSDUMMY1;

=> {<null>, ABCDEF} as expected.

SELECT
    coalesce(SUBSTR('ABCDEF', 1, LOCATE('.','ABCDEF')-1), 'DATA') as FIRST_PART
  , SUBSTR('ABCDEF', LOCATE('.','ABCDEF') 1)   as SECOND_PART
FROM SYSIBM.SYSDUMMY1;

=> still {<null>, ABCDEF}, not {DATA, ABCDEF} as expected.

Any idea how to get a char or varchar splittet into 2 colums by only one separator?

CodePudding user response：

If you look at the warnings returned when running the 3rd statement.

Argument *N of substringing function not valid. Cause . . . . . : Argument 2 or 3 of the SUBSTRING function, argument 2 of the LEFT function, or argument 3 of the OVERLAY function is either out of range or is an expression that does not evaluate to an integer.

LOCATE('.','ABCDEF') returns 0, so the third argument ends up being -1 which is not valid.

CASE is probably a better choice than COALESCE

SELECT
    case LOCATE('.','ABCDEF')
      when 0 then 'DATA'
      else SUBSTR('ABCDEF', 1,LOCATE('.','ABCDEF') -1)
    end  
  , SUBSTR('ABCDEF', LOCATE('.','ABCDEF') 1)   as SECOND_PART
FROM SYSIBM.SYSDUMMY1;