Using SUBSTR and CONCAT to get the required Result

Question

I Am trying to get the results for the 'CENTRE' whereby if the 'DEP2' does not exist it has to use 'DEP'

Have done this by using SUBSTR and CONCAT in my query But only get back 3 from 'DEP' when using it like this

My Query:

SELECT 
DEP,
DEP2,
SUBSTR(DEP2 || DEP,0,4)CENTRE

FROM DEPARTMENT

I Get the results back fine but with 'DEP' only showing 3 numbers instead of the 4

----------------------------------
DEP    | DEP2      | CENTRE      | 
----------------------------------
0132    | 7000     | 7000        |
0177    | 7000     | 7000        |
7051    | 7000     | 7000        |
7051    |          |  705        |
7051    |          |  705        | 
----------------------------------

Answer 1

If DEP2 is Null then DEP value will be returned

SELECT DEP, DEP2, NVL(DEP2, DEP) CENTRE
FROM DEPARTMENT;

Answer 2

if the 'DEP2' does not exist it has to use 'DEP'

Typical NVL/COALESCE situation, not something that should be handled with string functions.

SELECT DEP, DEP2, COALESCE(DEP2, DEP) CENTRE
FROM DEPARTMENT