OraFAQ Forum: SQL & PL/SQL » String Printing by PL/SQL code?

Home » SQL & PL/SQL » SQL & PL/SQL » String Printing by PL/SQL code? (10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

String Printing by PL/SQL code? [message #648628]

Sat, 27 February 2016 10:05

P1Oracle
Messages: 60
Registered: August 2014
Location: Hyderabad

Member

Hi friends,
I need a script which will print in below format:
/* INPUT STRING AS 'ORACLEFAQS'

and the final result should be 'ORACLEFAQS,ORACLEFAQ,ORACLEFA,ORACLEF,ORACLE,ORACL,ORAC,ORA'
Thanks in advance.

Report message to a moderator

Re: String Printing by PL/SQL code? [message #648629 is a reply to message #648628]

Sat, 27 February 2016 10:11

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

What does above have to do with Oracle?

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator

Re: String Printing by PL/SQL code? [message #648632 is a reply to message #648628]

Sat, 27 February 2016 10:33

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Is this a school homework assignment? If so, better post the code you have tried so far. Or at least describe how you would approach the problem.

Report message to a moderator

Re: String Printing by PL/SQL code? [message #648634 is a reply to message #648628]

Sat, 27 February 2016 10:48

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Hint: use a row generator.

Report message to a moderator

Re: String Printing by PL/SQL code? [message #648639 is a reply to message #648628]

Sat, 27 February 2016 12:44

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

The following is a 12c solution that uses a row generator and listagg as a string aggregator. Since listagg did not exist in 10g, you will have to find a 10g substitute, such as Tom Kyte's stragg function.

SCOTT@orcl> VARIABLE input_string VARCHAR2(30)
SCOTT@orcl> EXEC :input_string := 'ORACLEFAQS'

PL/SQL procedure successfully completed.

SCOTT@orcl> SELECT LISTAGG (string, ',') WITHIN GROUP (ORDER BY rn DESC) AS result
  2  FROM   (SELECT ROWNUM rn, SUBSTR (:input_string, 1, ROWNUM) AS string
  3  	     FROM   DUAL
  4  	     CONNECT BY LEVEL <= LENGTH (:input_string))
  5  WHERE  rn >= 3
  6  /

RESULT
--------------------------------------------------------------------------------
ORACLEFAQS,ORACLEFAQ,ORACLEFA,ORACLEF,ORACLE,ORACL,ORAC,ORA

1 row selected.

[Updated on: Sat, 27 February 2016 12:45]

Report message to a moderator

Re: String Printing by PL/SQL code? [message #648640 is a reply to message #648628]

Sat, 27 February 2016 14:14

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Just for fun without row generator on any kind:

VARIABLE input_string VARCHAR2(10)
EXEC :input_string := 'ORACLEFAQS'

SELECT  REGEXP_REPLACE(:input_string,'(^...)(.)(.)(.)(.)(.)(.)(.)','\1\2\3\4\5\6\7\8,\1\2\3\4\5\6\7,\1\2\3\4\5\6,\1\2\3\4\5,\1\2\3\4,\1\2\3,\1\2,\1') RESULT
  FROM  DUAL
/

RESULT
-----------------------------------------------------------
ORACLEFAQS,ORACLEFAQ,ORACLEFA,ORACLEF,ORACLE,ORACL,ORAC,ORA

SQL>

SY.

[Updated on: Sat, 27 February 2016 14:15]

Report message to a moderator

Re: String Printing by PL/SQL code? [message #648642 is a reply to message #648640]

Sat, 27 February 2016 14:35

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Report message to a moderator

Previous Topic:	Volume Apportioned to right volumen bands
Next Topic:	Finding the source of the Company

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 08:58:20 CDT 2024