Maximo

 View Only

Parse delimited string via Oracle SQL/regex (example: parse GL)

  • 1.  Parse delimited string via Oracle SQL/regex (example: parse GL)

    Posted Sat January 23, 2021 02:15 AM
    Edited by System Test Wed March 22, 2023 11:47 AM

    I thought I'd share a technique I came up with for parsing a delimited string (via Oracle SQL & registered expressions):


    Parse GL account string:

    select
        glaccount,
        regexp_substr(glaccount, '[^-]+', 1, 1) glseg1,
        regexp_substr(glaccount, '[^-]+', 1, 2) glseg2,
        regexp_substr(glaccount, '[^-]+', 1, 3) glseg3,
        regexp_substr(glaccount, '[^-]+', 1, 4) glseg4,
        regexp_substr(glaccount, '[^-]+', 1, 5) glseg5,
        regexp_substr(glaccount, '[^-]+', 1, 6) glseg6
    from
        workorder
    order by    
        length(glaccount) desc nulls last




    Or parse a hierarchy path:
    (a custom/persistent column in WORKORDER)

    classl1 = trim(regexp_substr(hierarchypath, '[^\]+', 1, 1)),
    classl2 = trim(regexp_substr(hierarchypath, '[^\]+', 1, 2)),
    classl3 = trim(regexp_substr(hierarchypath, '[^\]+', 1, 3)),
    classl4 = trim(regexp_substr(hierarchypath, '[^\]+', 1, 4));

    --the TRIM() function removes the spaces around the backslash delimiter

    I'd be happy to hear about any issues/mistakes.

    Cheers.

    #Maximo
    #AssetandFacilitiesManagement