CODEWITHSUNDEEP
oracle-databaseplsql
Tayyab
Tayyab

Reputation: 51

Oracle - removing starting zero's from string value

I am using a query 

SELECT CAST(000027 AS VARCHAR2(15)) FROM dual;

but this removes the starting 0000 zero's and return me 27 only.

How can I make it return 000027?

Upvotes: 0

Views: 419

Answers (2)

Alex Poole
Alex Poole

Reputation: 191295

Use a string literal instead of a numeric literal:

SELECT CAST('000027' AS VARCHAR2(15)) FROM dual

though the cast may be redundant anyway depending on what you are trying to do.

If you are stuck starting from a number then it's probably losing the leading zeros earlier than you think, as numbers don't actually have those.

If you know how many digits there should be then you can pad, or use to_char() instead of cast():

SELECT TO_CHAR(000027, 'FM000000') FROM dual

If you need to you can still cast that to varchar2(15), but not sure why what would be needed unless you're using this as part of a CTAS statement.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521499

The value 000027 and 27, both as numbers, are identical to Oracle, and it won't "see" the leading zeroes. The closest thing to what you want here might be to left pad the number/string with zeroes, to a fixed length. Assuming you want a numerical string of length 6, you could try:

SELECT LPAD(CAST(000027 AS VARCHAR2(15)), 6, '0')
FROM dual;

Demo

Upvotes: 1

Related Questions