1

The following string has some variables inside:

sText := '%CUSTOMER_LASTNAME%%CUSTOMER_PRENAME% - LANG: %LANGUAGE%'

I made a custom SPLIT_STRING function and want to get each variable in the String:

sStr := SPLIT_STRING(sText,'%');

What I now got in loop is:

  • null
  • CUSTOMER_LASTNAME
  • null
  • CUSTOMER_PRENAME,
  • '- Lang':
  • LANGUAGE
  • null

What I need:

  • CUSTOMER_LASTNAME
  • CUSTOMER_PRENAME
  • LANGUAGE

Can I perform a split string with an RegExp or how to do this?

TIA frgtv10

frgtv10
  • 5,040
  • 4
  • 28
  • 43

1 Answers1

2

Can I perform a split string with an RegExp

Yes, you can. Here is an example of how it can be done.

    SQL> with t1(col) as(
      2    select '%CUSTOMER_LASTNAME%%CUSTOMER_PRENAME%- LANG: %LANGUAGE%' from dual
      3  )
      4  , ocrs as(
      5     select level as ocr
      6       from ( select max(regexp_count(col, '%[^%]+%')) mxo
      7                from t1
      8             ) s
      9     connect by level <= s.mxo
     10  )
     11  select ltrim(rtrim(regexp_substr(col, '%[^%]+%', 1, o.ocr), '%'), '%') as res
     12    from t1
     13    cross join ocrs o
     14  ;

Result:

    RES
    --------------------
    CUSTOMER_LASTNAME
    CUSTOMER_PRENAME
    LANGUAGE

SQLFiddle Demo

Nick Krasnov
  • 26,363
  • 6
  • 58
  • 76
  • Wow, thats a bit weird for me. Is it possible to put it in a function? Do I really need the SELECT FROM DUAL part for doing this? – frgtv10 Sep 06 '13 at 12:29
  • wow.. i need to take a certification particularly for regexp :) – ajmalmhd04 Sep 06 '13 at 12:33
  • @frgtv10 The `with` clause is there just to represent sample of data you've provided - (to put it simply, *sort of table replacement*). – Nick Krasnov Sep 06 '13 at 12:35
  • This works, but how to put this in a function to use it maybe like the `SPLIT_STRING` function? – frgtv10 Sep 06 '13 at 12:40
  • 1
    @ajmalmhd04 lol, maybe SO can start offering certifications. Until then, here's some [fun reading](http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_regexp.htm) to get you going. – tbone Sep 06 '13 at 13:03
  • Thanks tbone and Nicholas – ajmalmhd04 Sep 07 '13 at 17:56