1 Reply Latest reply on Jun 5, 2015 10:31 AM by Sanjaya Kumar Pradhan

    Extrcat values from string in oracle

    858328

      Hi ALL,

      I have a below input string, I want to extract the value in between ORDER and DATA_CHECK_LIST and the value is DEV_D_1456789.

      How can we extract the value (DEV_D_1456789) in between ORDER and DATA_CHECK_LIST?

      For any other string also we have to find the value in between ORDER and DATA_CHECK_LIST

       

      Input string:

       

      DATA_MAP|111||POINT|678||CLOSED||KR|****| PTNR| |LOACTION||DATA|| ADDRESS|||ANOTHER||ADDRESS|ANOTHER||||MYCITY|NL||||||||||||

      CHTR|NETWORK|US_EXP||||||||||| |||CHTR|ROYAL|| SHIP_FROM||||||||||

      ADDRESS|ANOTHER||||MYCITY|NL|||||||||||||||||||||||||||||

      ORDER|DEV_889|DATA_LOST||QQQQ||||

      ORDER|DEV_D_1456789|DATA_CHECK_LIST|| ADDRESS|||ANOTHER |

      ||||||||||||||||||||||||||||ARL|||||||||||||||||||||||||||HHHHH|||||||||||||

      TTTTGGH||||


      Could you please help me on this either can use regular expression or instr.


      Thanks

        • 1. Re: Extrcat values from string in oracle
          Sanjaya Kumar Pradhan

          Hi,

           

          I have quickly written this query, it can be improved

           

            select REPLACE(

                                      REGEXP_SUBSTR( (SELECT

            REGEXP_SUBSTR('DATA_MAP|111||POINT|678||CLOSED||KR|****| PTNR| |LOACTION||DATA|| ADDRESS|||ANOTHER||ADDRESS|ANOTHER||||MYCITY|NL||||||||||||CHTR|NETWORK|US_EXP||||||||||| |||CHTR|ROYAL|| SHIP_FROM||||||||||ADDRESS|ANOTHER||||MYCITY|NL|||||||||||||||||||||||||||||ORDER|DEV_889|DATA_LOST||QQQQ||||ORDER|DEV_D_1456789|DATA_CHECK_LIST|| ADDRESS|||ANOTHER |||||||||||||||||||||||||||||ARL|||||||||||||||||||||||||||HHHHH|||||||||||||TTTTGGH||||

          ','ORDER\|[^|]+\|DATA_CHECK_LIST') "REGEXPR_SUBSTR" FROM DUAL),'\|[^|]+\|')

                   ,'|')from dual;

           

          Hope this is what you are looking for.

           

          please show a meaningful name '858328'.

           

          Mark correct answer if it resolves your issue.

           

          Thanks,

          Sanjaya