Home » SQL & PL/SQL » SQL & PL/SQL » replicate sql problem
replicate sql problem [message #446707] Wed, 10 March 2010 01:11 Go to next message
anne_p18
Messages: 8
Registered: March 2010
Location: Philippines
Junior Member
Hi,

I need you help..

Im having a problem to my output. I convert the codes from sybase to oracle

SYBASE:
if @unique_cnt < 100

select @unique_cnt_str = replicate('0',(3-datalength(ltrim(rtrim(convert(char(2), @unique_cnt)))))) + ltrim(rtrim(convert(char(2), @unique_cnt)))

else
select @unique_cnt_str = ltrim(rtrim(convert(char(3), @unique_cnt)))


ORACLE:

BEGIN
IF v_unique_cnt < 100 THEN
v_unique_cnt_str := LPAD('',(3 - LENGTHB(LTRIM(RTRIM(CAST(v_unique_cnt AS CHAR(2)))))),'0') || LTRIM(RTRIM(CAST(v_unique_cnt AS CHAR(2))));

ELSE
v_unique_cnt_str := LTRIM(RTRIM(CAST(v_unique_cnt AS CHAR(3))));

END IF;

END;


The problem is when v_unique_cnt is less than 100 let say 2.
The output MUST be 002 but im getting the output 2.
and so as when v_unique_cnt is 34, im getting 34 not 034.

the zero in the left of v_unique_cnt disappear.

Please help with this..
Thanks!!
Re: replicate sql problem [message #446709 is a reply to message #446707] Wed, 10 March 2010 01:14 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
First convert your number to a string (= TO_CHAR), then fill it out with zeroes on the left-hand side (= LPAD)
SQL> select lpad(to_char(34), 3, '0') from dual;

LPA
---
034
Re: replicate sql problem [message #446711 is a reply to message #446707] Wed, 10 March 2010 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want a specific format then tell it to TO_CHAR:
SQL> select to_char(34,'fm000') from dual;
TO_C
----
034

Regards
Michel
Re: replicate sql problem [message #446712 is a reply to message #446711] Wed, 10 March 2010 01:26 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
doh!
Been away from db-programming for too long, I guess Smile
Previous Topic: Converting HTML format text to Plain Text
Next Topic: CURSOR in ORACLE
Goto Forum:
  


Current Time: Sun Dec 11 08:09:50 CST 2016

Total time taken to generate the page: 0.20933 seconds