Link to home
Start Free TrialLog in
Avatar of sinwee80
sinwee80

asked on

Replicate function equivalent in Oracle 7

Hi,

Is anyone knoes equivalent of Replicate function in SQL in Oracle 7??

Any help will be very very much appreciated!!

SinWee
Avatar of konektor
konektor
Flag of Czechia image

what does the function do ?
provide an example ...
Avatar of sinwee80
sinwee80

ASKER

I'm currently converting Sql stored procedure to Oracle Stored Procedure.I hav this line

select @v_lv_e_field = replicate('0',5-len(rtrim(ltrim(@v_e_field))))+rtrim(ltrim(@v_e_field))

which doing this:
If my @v_e_field having less then 5 digit then it will concatenate 0 string in front of value @v_e_field  to make @v_lv_e_field up to 5 characters.
try this.,

rpad( '', 5-len(rtrim(ltrim(@v_e_field))), 0 )  +rtrim(ltrim(@v_e_field), '0' ) )

itsvtk
i have seen this RPAD function somewhere,but  is this function supported in oracle 7?
try this

rpad( '0', 5-len(rtrim(ltrim(@v_e_field)))-1, 0 )  +rtrim(ltrim(@v_e_field), '0' ) )

itsvtk
or try this

ltrim( rpad( ' ', 6-len(rtrim(ltrim(@v_e_field))), 0 ) ) + rtrim(ltrim(@v_e_field), '0' ) )

itsvtk
try this once, if doesn't support let me know, i will find alternative for that

itsvtk
sorry above one is wrong just now i tried in my oracle

but this will work

LTRIM( RPAD( ' ', 6-LENGTH(rtrim(ltrim(@v_e_field))), 0 ) ) || rtrim(ltrim(@v_e_field))

itsvtk




Hi,

I do not have oracle 7 here. Can u try for me is this working?

lv_e_field := lpad(rtrim(ltrim(@v_e_field)), 5, '0');

SinWee
lv_e_field := lpad(rtrim(ltrim(@v_e_field)), 5, '0');


no this will not work..... if the length of the field greater than 5( it returns always 5 characters )

LTRIM( RPAD( ' ', 6-LENGTH(rtrim(ltrim(@v_e_field))), 0 ) ) || rtrim(ltrim(@v_e_field))

this will work... i tested its working .....

itsvtk

I don't have oracle 7.0 in my sys.... i have oracle 8i.....  

but i have confident that it works in 7...

itsvtk
Hi itsvtk

why is this using 6-length of @v_e_field???
LTRIM( RPAD( ' ', 6-LENGTH(rtrim(ltrim(@v_e_field))), 0 ) ) || rtrim(ltrim(@v_e_field))

since the old sql sp is doing
select @v_lv_e_field = replicate('0',5-len(rtrim(ltrim(@v_e_field))))+rtrim(ltrim(@v_e_field))
Hi itsvtk,

let's say if my @v_e_field won't have more than 5 digits, so can i stil use this:
lv_e_field := lpad(rtrim(ltrim(@v_e_field)), 5, '0');
You you need to get at least 5 characters left padded by 0 in a case of a "short" initial string
I will offer you to use the following

v_lv_e_field = lpad(nvl(@v_e_field,'0'),greatest(5,length(nvl(@v_e_field,'0')),'0');

it will completely cover all possible situations in your case

Good luck
ASKER CERTIFIED SOLUTION
Avatar of Thandava Vallepalli
Thandava Vallepalli
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Also, if you need to eliminate all unnecessary leading and tailing spaces you can use:
v_lv_e_field := lpad(nvl(trim(@v_e_field),'0'),greatest(5,length(nvl(trim(@v_e_field),'0')),'0');
or separately
v_lv_e_field := nvl(trim(@v_e_field),'0');
v_lv_e_field := lpad(v_lv_e_field,greatest(5,length(v_lv_e_field)),'0');

if "trim" does not exists in Oracle 7, try ltrim(rtrim(@v_e_field)) instead.