Sunday, February 19, 2017

Split word from sentence using oracle SQL Query

SQL> SELECT LEVEL, REGEXP_SUBSTR('RAJIB NARSINGDI DHAKA BANGLADESH','[^ ]+', 1, LEVEL) FROM DUAL
 CONNECT BY REGEXP_SUBSTR('RAJIB NARSINGDI DHAKA BANGLADESH', '[^ ]+', 1, LEVEL) IS NOT NULL;  2

     LEVEL REGEXP_SUBSTR('RAJIBNARSINGDIDHA
---------- --------------------------------
         1 RAJIB
         2 NARSINGDI
         3 DHAKA
         4 BANGLADESH

SQL> SELECT ROWNUM SL, EXTRACTVALUE(XT.COLUMN_VALUE, 'e') COLUMN_VALUES
  2              FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE('<coll><e>' ||
  3                                                     REPLACE(REPLACE(REPLACE('RAJIB NARSINGDI DHAKA BANGLADESH','&',''),':',''),
  4                                                             ' ',
  5                                                             '</e><e>') ||
  6                                                     '</e></coll>'),
  7                                             '/coll/e'))) XT;

     SL COLUMN_VALUES
---------- --------------------------------
         1 RAJIB
         2 NARSINGDI
         3 DHAKA
         4 BANGLADESH

SQL>

3 comments:

  1. Hi,
    This is so simple way of presenting the article and content is also nice ,the way splitting of word from sentence by using sql query.
    thank you.
    oracle HCM training

    ReplyDelete
  2. Hi,
    this is very interesting topic.
    thanks for sharing such a nice topic.
    oracle fusion hCM online training

    ReplyDelete