I have a variable in Oracle that contains a string. This string has many words separated by $. I want to know if I can use Split Function to get the list of word.
Example:
Suppose that [variable] contains (hello$wordl$stack$overflow)
SELECT COLUMN_VALUE AS Description FROM TABLE( split_String( variable ) );
Description
----------------
hello
world
stack
overflow
Yes, just define a split_string function to do it:
CREATE OR REPLACE TYPE stringlist AS TABLE OF VARCHAR2(20)
/
CREATE OR REPLACE FUNCTION split_String(
i_str IN VARCHAR2,
i_delim IN VARCHAR2 DEFAULT ','
) RETURN stringlist DETERMINISTIC
AS
p_result stringlist := stringlist();
p_start NUMBER(5) := 1;
p_end NUMBER(5);
c_len CONSTANT NUMBER(5) := LENGTH( i_str );
c_ld CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
IF c_len > 0 THEN
p_end := INSTR( i_str, i_delim, p_start );
WHILE p_end > 0 LOOP
p_result.EXTEND;
p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );
p_start := p_end + c_ld;
p_end := INSTR( i_str, i_delim, p_start );
END LOOP;
IF p_start <= c_len + 1 THEN
p_result.EXTEND;
p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );
END IF;
END IF;
RETURN p_result;
END;
/
Then:
SELECT COLUMN_VALUE AS Description
FROM TABLE( split_String( 'hello$world$stack$overflow', '$' ) );
If you want to use a bind variable instead of a literal then you can:
SELECT COLUMN_VALUE AS Description
FROM TABLE( split_String( :variable, '$' ) );
(or ? for an anonymous bind variable.)
Outputs:
DESCRIPTION hello world stack overflow
db<>fiddle here
I created a table name emp with one column name.
Following are the SQL i have used.
create table emp (name varchar2(20));
insert into emp values('Hello$WOrld$User');
SELECT LEVEL AS id, REGEXP_SUBSTR(emp.name, '[^$]+', 1, LEVEL) AS data
FROM emp
CONNECT BY REGEXP_SUBSTR(emp.name, '[^$]+', 1, LEVEL) IS NOT NULL;
| ID | DATA |
|---|---|
| 1 | Hello |
| 2 | world |
| 3 | User |
My solution is this one:
CREATE OR REPLACE TYPE VARCHAR_TABLE_TYPE AS TABLE OF VARCHAR2(1000);
CREATE OR REPLACE FUNCTION SplitString(
LIST IN VARCHAR2,
Separator IN VARCHAR2 DEFAULT ';',
skipNulls IN BOOLEAN DEFAULT TRUE)
RETURN VARCHAR_TABLE_TYPE IS
OutTable VARCHAR_TABLE_TYPE;
items INTEGER;
item VARCHAR2(30000);
BEGIN
IF LIST IS NULL THEN
RETURN NULL;
ELSE
IF LENGTH(LIST) < 4000 THEN
IF skipNulls THEN
SELECT TRIM(REGEXP_SUBSTR(LIST, '[^'||Separator||']+', 1, LEVEL))
BULK COLLECT INTO OutTable
FROM dual
CONNECT BY REGEXP_SUBSTR(LIST, '[^'||Separator||']+', 1, LEVEL) IS NOT NULL;
ELSE
SELECT TRIM(RTRIM(REGEXP_SUBSTR(LIST||Separator, '.*?'||Separator, 1, LEVEL), Separator))
BULK COLLECT INTO OutTable
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(LIST, Separator)+1;
END IF;
ELSE
-- Otherwise I get ORA-01460: unimplemented or unreasonable conversion requested
items := REGEXP_COUNT(LIST, Separator) + 1;
IF items = 1 THEN
RETURN VARCHAR_TABLE_TYPE(LIST);
ELSE
OutTable := VARCHAR_TABLE_TYPE();
IF skipNulls THEN
FOR i IN 1..items LOOP
item := REGEXP_SUBSTR(LIST, '[^'||Separator||']+', 1, i);
IF item IS NOT NULL THEN
OutTable.EXTEND;
OutTable(OutTable.LAST) := TRIM(item);
END IF;
END LOOP;
ELSE
OutTable.EXTEND(items);
FOR i IN 1..items LOOP
item := RTRIM(REGEXP_SUBSTR(LIST||Separator, '.*?'||Separator, 1, i), Separator);
OutTable(i) := TRIM(item);
END LOOP;
END IF;
END IF;
END IF;
END IF;
IF OutTable.COUNT > 0 THEN
RETURN OutTable;
ELSE
RETURN NULL;
END IF;
END SplitString;
