Search spaces in a string
find the position of every space in string.
Last updated:
Warning: Review and test in a non-production environment before running.
SQL> with test (movies) as
2 (select 'The Lord Of The Ring' from dual union all
3 select 'Age Of ultron' from dual
4 )
5 select movies,
6 instr(movies, ' ', 1, column_value) space_position
7 from test,
8 table(cast(multiset(select level
9 from dual
10 connect by level <= regexp_count(movies, ' ')
11 ) as sys.odcinumberlist))
12 order by movies, space_position;
MOVIES SPACE_POSITION
-------------------- --------------
Age Of ultron 4
Age Of ultron 7
The Lord Of The Ring 4
The Lord Of The Ring 9
The Lord Of The Ring 12
The Lord Of The Ring 16
6 rows selected.
SQL>