Banner Ad

    A    L    W    A    Y    S           D    E    S    I    R    E           T    O           L    E    A    R    N           S    O    M    E    T    H    I    N    G           U    S    E    F    U    L   

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');

0 comments:

Related Posts Plugin for WordPress, Blogger...

your comments