Pages - Menu

Pages

Monday, June 6, 2016

How to split word by new line character in PLSQL / SQL query with regular expressions




Convert above single lengthy word into several rows as following. 
Above 7 lines in single text box will take as 7 new lines as per below code.


Following is the PL-SQL cursor  to split single word into several words based on new line character

1.)   CHR(10) -- Line feed
2.)   CHR(13) -- Carriage return.

1
2
3
4
CURSOR get_work_desc(str_ VARCHAR2) IS
      SELECT
      regexp_substr(str_,'[^*' || chr(13) || chr(10) || ')]+',1,level,'m') AS work_description from 
dual connect by level <= regexp_count(str_,'[^*' || chr(13) || chr(10) || ')]+',1,'m');

No comments:

Post a Comment