String Printing by PL/SQL code? [message #648628] |
Sat, 27 February 2016 10:05 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/fac97858265c5d9fa923354c520a2317?s=64&d=mm&r=g) |
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.
|
|
|
|
|
|
Re: String Printing by PL/SQL code? [message #648639 is a reply to message #648628] |
Sat, 27 February 2016 12:44 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Solomon Yakobson
Messages: 3275 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
|
|
|
|