Leading Zeros getting truncated using SUBSTRING

Question

One of the values in the column is
089-002007

I wish to extract all the numbers after '-'

SELECT SUBSTR(EMP_NO,5)
FROM Table_Name  

However, I get the output as '2007', the leading zeros got truncated. I have multiple values where it starts with 0 after the '-'.

how can I fix this?

Answer 1

Looks like it's implicitly changing the result to int, hence chopping off leading zeroes. I would suggest CAST-ing the SELECT

SELECT CAST(SUBSTR(EMP_NO,5) AS VARCHAR(10))
FROM Table_Name