IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this forum will no longer be available. More details available on our FAQ.
Topic
  • 1 reply
  • Latest Post - ‏2018-10-24T12:01:29Z by Jordi.Hoffmann
Jordi.Hoffmann
Jordi.Hoffmann
33 Posts

Pinned topic Question about REGEXP_SUBSTR (V7R1)

‏2018-10-11T08:32:10Z |

Hello,

can somebody please explain why this works:

select regexp_substr('100 QT', '(\d+)\s?([a-z]+)', 1, 1, 'i', 1),
       regexp_substr('100 QT', '(\d+)\s?([a-z]+)', 1, 1, 'i', 2)
from   sysibm.sysdummy1                                          

==>

REGEXP_SUBSTR  REGEXP_SUBSTR
   100            QT        


and this doesn't?

with data (s) as (values ('100  QT'))                     
select regexp_substr(s, '(\d+)\s?([a-z]+)', 1, 1, 'i', 1),
       regexp_substr(s, '(\d+)\s?([a-z]+)', 1, 1, 'i', 2)
from   data                                               

==>

REGEXP_SUBSTR  REGEXP_SUBSTR
   -              -          


I tried casting s to char, varchar and dbclob, but neither worked.

Thank you in advance

Jordi

  • Jordi.Hoffmann
    Jordi.Hoffmann
    33 Posts
    ACCEPTED ANSWER

    Re: Question about REGEXP_SUBSTR (V7R1)

    ‏2018-10-24T12:01:29Z  

    Never mind, it does work nicely like this:

    with data (s) as (values ('''100 QT'''))                  
    select regexp_substr(s, '(\d+)\s?([a-z]+)', 1, 1, 'i', 1),
           regexp_substr(s, '(\d+)\s?([a-z]+)', 1, 1, 'i', 2)
    from   data                                              

    ==>

    REGEXP_SUBSTR  REGEXP_SUBSTR
      100            QT        

    Jordi

     

  • Jordi.Hoffmann
    Jordi.Hoffmann
    33 Posts

    Re: Question about REGEXP_SUBSTR (V7R1)

    ‏2018-10-24T12:01:29Z  

    Never mind, it does work nicely like this:

    with data (s) as (values ('''100 QT'''))                  
    select regexp_substr(s, '(\d+)\s?([a-z]+)', 1, 1, 'i', 1),
           regexp_substr(s, '(\d+)\s?([a-z]+)', 1, 1, 'i', 2)
    from   data                                              

    ==>

    REGEXP_SUBSTR  REGEXP_SUBSTR
      100            QT        

    Jordi