SQL Language (MOSC)

MOSC Banner

Select strips leading zero when using cast function

edited Jun 30, 2010 2:32AM in SQL Language (MOSC) 12 commentsAnswered
SELECT INS_EMP_FEIN FROM TABLE(CAST(POC_INSURER_EMPLOYER_Q1('','FALSE','','','020170100','') AS poc_pol_emp_table_type))

INS_EMP_FEIN
------------
    20170100
    20170100
    20170100
    20170100
    20170100
    20170100
    20170100

we have tried a lpad but give us this result:

SELECT lpad(INS_EMP_FEIN,9,'0') FROM TABLE(CAST(POC_INSURER_EMPLOYER_Q1('','FALSE','','','020170100','') AS poc_pol_emp_table_type))

LPAD(INS_
---------
020170100
020170100
020170100
020170100
020170100

How do we get Oracle to display leading zeros when the column is defined as varchar2(9) in the database?

Thanks.

Bruce M

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center