Home » SQL & PL/SQL » SQL & PL/SQL » substr truncates one byte
substr truncates one byte [message #208187] Fri, 08 December 2006 08:53 Go to next message
mickle
Messages: 2
Registered: December 2006
Junior Member
I am selecting an 8 byte number column but the result only displays 7 bytes.

Example

SELECT cust_id FROM customer;

202000


SELECT substr(to_char(cust_id,'09999999'),1,8) FROM customer;

0020200

The result I am looking for is 00202000.
Can someone please explain to me why this is happening?

Thanks in advance.
Re: substr truncates one byte [message #208188 is a reply to message #208187] Fri, 08 December 2006 09:04 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Why not use LPAD:

SQL> select lpad(202000, 8, 0) from dual;

LPAD(202
--------
00202000
Re: substr truncates one byte [message #208190 is a reply to message #208188] Fri, 08 December 2006 09:08 Go to previous messageGo to next message
mickle
Messages: 2
Registered: December 2006
Junior Member
Thank you!
Re: substr truncates one byte [message #208192 is a reply to message #208187] Fri, 08 December 2006 09:09 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your format mask is providing you with a leading space to put the - sign in if one is required. The 8 chrs you select include this space.
Try
SELECT 'X'||to_char(cust_id,'09999999')||'X' FROM customer;
to see what I mean.

Use 'FM09999999' as a format mask to avoid this.
Previous Topic: Using INSTR function
Next Topic: help with dbms_aqadm package
Goto Forum:
  


Current Time: Fri Dec 02 16:26:52 CST 2016

Total time taken to generate the page: 0.39438 seconds