Home » SQL & PL/SQL » SQL & PL/SQL » Neither LPAD nor RPAD - is there a middle? (merged)
Neither LPAD nor RPAD - is there a middle? (merged) [message #211599] Fri, 29 December 2006 15:50 Go to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
SELECT RPAD('1000103000700005001', 4, '-', 25) -, 9, '.', 12, '-', 15, '.', 18, '-', 22, '.')
FROM sometable
where rownum <=2

The output should look like this:
1000-103.00-33.55-002.111 for a total of 25 bytes
note the column is a varchar2(27) extra 2 bytes for extra characters.

Please help
Re: Neither LPAD NOR RPAD is there a middle ?? [message #211608 is a reply to message #211599] Fri, 29 December 2006 18:07 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
SQL> select substr(x, 1, 4)
  2     ||'-'||substr(x,5,3)
  3     ||'.'||substr(x,8,2)
  4     ||'-'||substr(x,10,2)
  5     ||'.'||substr(x,12,2)
  6     ||'-'||substr(x,14,3)
  7     ||'.'||substr(x,17) value
  8  from (select '1000103000700005001' x from dual);

VALUE
-------------------------
1000-103.00-07.00-005.001


If you are on 10g, you can consider:

SQL> select regexp_replace(x,'(\d{4})(\d{3})(\d{2})(\d{2})(\d{2})(\d{3})'
  2     ,'\1-\2.\3-\4.\5-\6.') value
  3  from (select '1000103000700005001' x from dual);

VALUE
-------------------------
1000-103.00-07.00-005.001


Modular Add ON [message #211863 is a reply to message #211599] Tue, 02 January 2007 13:38 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Thanks Brian. This is a huge project and I am trying to use modular pieces to avoid crating a mess. I am new to oracle/ PL/SQL but can hold my own in a few structured lang.

Prior to the conversion I executed this line of code:

select somenumber = '&someNumber'; -- usr input
that # is converted and stored in @var.

can I use code below?
DECLARE
@somenumber varchar2(19) -- not null
BEGIN
SET @var = '' -- pending user input
end
select '&somenumber:' substr(x, 1, 4) -- user input converted
||'-'||substr(x,5,3)
||'.'||substr(x,8,2)
||'-'||substr(x,10,2)
||'.'||substr(x,12,2)
||'-'||substr(x,14,3)
||'.'||substr(x,17) value --I want value stored not displayed
from x from table) -- I need to take stored value and then qry master db and return result to usr.

Please help!
Modular ADD ON - PL/SQL or Native SQL*Plus [message #211868 is a reply to message #211599] Tue, 02 January 2007 15:09 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Here is pseudocode of what my goal is?

DECLARE
@var varchar2(19) -- Id # not null
BEGIN
SET @var = '' -- pending user input
end

--Prior to the conversion I executed this line of code:

select @var = '&someNumber'; -- usr input

-- Capture usr input and convert by adding '-' & '.'
select '&somenumber:' substr(x, 1, 4) -- user input converted
||'-'||substr(x,5,3)
||'.'||substr(x,8,2)
||'-'||substr(x,10,2)
||'.'||substr(x,12,2)
||'-'||substr(x,14,3)
||'.'||substr(x,17) value from x from table) --I want above
-- value stored not displayed. Then, I need to take stored value
-- and then qry master db and return result to usr. Result is to be returned 3 rec at a time to a max of 15 (loop 5x) Then exit, Plus add some exception code to test for null and the likes.

My big Q is, should I use several functions/procedures for each task and call them as needed or can this be done in native SQL*Plus?

Your input is much appreciated.

Thanks In advance!
Re: Modular ADD ON - PL/SQL or Native SQL*Plus [message #211870 is a reply to message #211868] Tue, 02 January 2007 15:27 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
IMO, you are using the wrong tool for the job.
SQL*Plus is a lousy user interface tool.
User input directly into SQL*Plus is a HUGE security hole!
Proceed at your own risk & I refuse to help you dig your own grave.
Re: Modular ADD ON - PL/SQL or Native SQL*Plus [message #212045 is a reply to message #211870] Wed, 03 January 2007 11:02 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Hi,

I do respect your input, but any suggestion/direction at all?

Many Thanks!
Re: Modular ADD ON - PL/SQL or Native SQL*Plus [message #212047 is a reply to message #212045] Wed, 03 January 2007 11:18 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Actually, I forgot to mention usr input is for testing the code only. The final product will get that input from elsewhere (different dept.)stored it into a variable and pass it to me. I would then, have to get that value and do what needs to be done afterwards and give back the result.

Sorry for not being clear on that minor subject. Which is not so minor after all in retrospect. Nevertheless, I can deal with any hole since it is a in house test for the time being.

Regards,
Re: Modular ADD ON - PL/SQL or Native SQL*Plus [message #212161 is a reply to message #212047] Thu, 04 January 2007 01:03 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Then create your testscenario to reflect the real scenario.
Will you eventually use this in a stored program unit? Then write the definitive version and use a testscript to call it.

And by the look of your code.. Are you on Oracle or on SQL-Server/Sybase?
Re: Modular ADD ON - PL/SQL or Native SQL*Plus [message #212287 is a reply to message #212161] Thu, 04 January 2007 09:13 Go to previous message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Hi Frank,

You Wrote: > "Will you eventually use this in a stored program unit? Then write the definitive version and use a testscript to call it.?"

That was my precise Q. Right now I am using native SQL*Plus trying to make each piece work thus, avoid creating a mess. I am new to this and I want to proceed w/ caution. It 'll be decided later on which approach will be faster in terms of throughput.
I was wondering which of the 2 would be a better approach (my idea). I do know though, functions would have to be the way to go if I am going modular since I need to return result back to usr.

I am on Oracle 9.x.

Many Thanks!
Previous Topic: Group by on a view
Next Topic: coulmn
Goto Forum:
  


Current Time: Thu Dec 08 12:22:43 CST 2016

Total time taken to generate the page: 0.13420 seconds