How can I parse a string into tokens in PL/SQL?
For example, this is my string:
170823-14785
and the parsing I want is:
17
08
23
-1
47
85
4 Answers
As I see, you want replace all consecutive 2 chars, with following space symbol
If so, then something like this: (No PL/SQL needed for this)
SELECT REGEXP_REPLACE('170823-14785', '(.{2})', '\1 ') FROM DUAL;
Comments
Assuming you want to split a string into tokens of 2 characters each, no matter the content of the string, this can be a way, with no need for PL/SQL:
select substr(str, (level-1)*2 +1, 2)
from (
select '170823-14785' str
from dual
)
connect by 2*level -1 <= length(str)
order by level
Which gives:
SUBSTR(S
--------
17
08
23
-1
47
85
Adding something to the select list will clarify how this works:
select substr(str, (level-1)*2 +1, 2) as token,
level,
(level-1)*2 +1 as startPos
from (
select '170823-14785' str
from dual
)
connect by 2*level -1 <= length(str)
order by level
TOKEN LEVEL STARTPOS
-------- ---------- ----------
17 1 1
08 2 3
23 3 5
-1 4 7
47 5 9
85 6 11
Comments
Use SUBSTR( string, position, substring_length ):
DECLARE
v_input VARCHAR2(20) := '170823-14785';
v_str1 CHAR(2);
v_str2 CHAR(2);
v_str3 CHAR(2);
v_str4 CHAR(2);
v_str5 CHAR(2);
v_str6 CHAR(2);
BEGIN
v_str1 := SUBSTR( v_input, 1, 2 );
v_str2 := SUBSTR( v_input, 3, 2 );
v_str3 := SUBSTR( v_input, 5, 2 );
v_str4 := SUBSTR( v_input, 7, 2 );
v_str5 := SUBSTR( v_input, 9, 2 );
v_str6 := SUBSTR( v_input, 11, 2 );
END;
/
Comments
This example uses REGEXP_SUBSTR() and handles an odd number of characters by making the second character optional and makes the group of 2 characters valid when followed by a character or the end of the string. Always expect the unexpected!
SQL> with tbl(str) as (
select '170823-147854' from dual
)
select level, regexp_substr(str, '((.)(.)?)(.|$)', (((level-1)*2)+1), 1, NULL, 1) parsed
from tbl
connect by level <= round(length(str)/2)
order by level;
LEVEL PARSED
---------- -------------
1 17
2 08
3 23
4 -1
5 47
6 85
7 4
7 rows selected.
SQL>
SUBSTR(i, 2)where i = 1,3,5,7,9,... untili >= LENGTH(str)docs.oracle.com/cd/B19306_01/server.102/b14200/functions162.htm