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
Is anyone knoes equivalent of Replicate function in SQL in Oracle 7??
Any help will be very very much appreciated!!
SinWee
equivalent of Replicate = RPAD / LPAD
http://www.adp-gmbh.ch/ora/sql/rpad.html
http://www.techonthenet.com/oracle/functions/rpad.htm
itsvtk
http://www.adp-gmbh.ch/ora/sql/rpad.html
http://www.techonthenet.com/oracle/functions/rpad.htm
itsvtk
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(ltr im(@v_e_fi eld))
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.
select @v_lv_e_field = replicate('0',5-len(rtrim(
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_fie ld))), 0 ) +rtrim(ltrim(@v_e_field), '0' ) )
itsvtk
rpad( '', 5-len(rtrim(ltrim(@v_e_fie
itsvtk
ASKER
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_fie ld)))-1, 0 ) +rtrim(ltrim(@v_e_field), '0' ) )
itsvtk
rpad( '0', 5-len(rtrim(ltrim(@v_e_fie
itsvtk
or try this
ltrim( rpad( ' ', 6-len(rtrim(ltrim(@v_e_fie ld))), 0 ) ) + rtrim(ltrim(@v_e_field), '0' ) )
itsvtk
ltrim( rpad( ' ', 6-len(rtrim(ltrim(@v_e_fie
itsvtk
try this once, if doesn't support let me know, i will find alternative for that
itsvtk
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
but this will work
LTRIM( RPAD( ' ', 6-LENGTH(rtrim(ltrim(@v_e_
itsvtk
ASKER
Hi,
I do not have oracle 7 here. Can u try for me is this working?
lv_e_field := lpad(rtrim(ltrim(@v_e_fiel d)), 5, '0');
SinWee
I do not have oracle 7 here. Can u try for me is this working?
lv_e_field := lpad(rtrim(ltrim(@v_e_fiel
SinWee
lv_e_field := lpad(rtrim(ltrim(@v_e_fiel d)), 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
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_
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
but i have confident that it works in 7...
itsvtk
ASKER
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(ltr im(@v_e_fi eld))
why is this using 6-length of @v_e_field???
LTRIM( RPAD( ' ', 6-LENGTH(rtrim(ltrim(@v_e_
since the old sql sp is doing
select @v_lv_e_field = replicate('0',5-len(rtrim(
ASKER
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_fiel d)), 5, '0');
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_fiel
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'),g reatest(5, length(nvl (@v_e_fiel d,'0')),'0 ');
it will completely cover all possible situations in your case
Good luck
I will offer you to use the following
v_lv_e_field = lpad(nvl(@v_e_field,'0'),g
it will completely cover all possible situations in your case
Good luck
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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'),great est(5,leng th(nvl(tri m(@v_e_fie ld),'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_fie ld)),'0');
if "trim" does not exists in Oracle 7, try ltrim(rtrim(@v_e_field)) instead.
v_lv_e_field := lpad(nvl(trim(@v_e_field),
or separately
v_lv_e_field := nvl(trim(@v_e_field),'0');
v_lv_e_field := lpad(v_lv_e_field,greatest
if "trim" does not exists in Oracle 7, try ltrim(rtrim(@v_e_field)) instead.
provide an example ...