I have a string like below:
Hello Tom
Where are you
What's your name
I want to split the above string with new line and add to array as below :
[Hello Tom,Where are you,What's your name]
Is this possible in PL/SQL ?
Add a comment
|
2 Answers
Why do you ask Tom's name again when you have already said hello to him?
Anyways.. Yes. You could use the standard string split operation on string delimited by CHR(10) - newline character in Oracle. Then make use of CAST and COLLECT functions to convert it to an array. Here I have used Oracle's built-in collection sys.OdciVarchar2List. In your PL/SQL block you may BULK COLLECT it into any proper collection type that can hold string elements.
WITH t (s)
AS (
SELECT 'Hello Tom
Where are you
What''s your name'
FROM DUAL
)
SELECT CAST ( COLLECT ( REGEXP_SUBSTR(s, '[^' || CHR(10)|| ']+', 1, LEVEL) )
AS sys.OdciVarchar2List ) as collection
FROM t CONNECT BY LEVEL <= REGEXP_COUNT(s, '[^' || CHR(10)|| ']+')
| COLLECTION |
|------------------------------------------|
| Hello Tom,Where are you,What's your name |
Comments
Kaushik is probably right and I'm probably wrong, but that's what I understood (by looking at what the OP enclosed into square brackets, as a desired result):
SQL> WITH t (s)
2 AS (SELECT 'Hello Tom
3 Where are you
4 What''s your name'
5 FROM DUAL
6 )
7 select replace(s, chr(10), ',') result
8 from t;
RESULT
----------------------------------------
Hello Tom,Where are you,What's your name
SQL>
