CODEWITHSUNDEEP
sqloracle-databaseplsql
Vara Mahalakhmi
Vara Mahalakhmi

Reputation: 31

Split String with new line and add in array in PL sql Oracle

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 ?

Upvotes: 3

Views: 2494

Answers (2)

Littlefoot
Littlefoot

Reputation: 142705

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>

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

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.

SQL Fiddle

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)|| ']+')

Results:

|                               COLLECTION |
|------------------------------------------|
| Hello Tom,Where are you,What's your name |

Upvotes: 4

Related Questions