Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Finding the alphabets from a Alphanumeric in OTBI.

Received Response
181
Views
2
Comments
satya_nvs
satya_nvs Rank 2 - Community Beginner
in Oracle Transactional Business Intelligence

Summary: I have a requirement to identify the alphabets (be it upper/lower case) from a string. The format of the sting is as follows:

SSN: XXX-XX-XXXX (112-10-1234)

TIN: XX-XXXXXXX (10-1234567)

The valid scenario is it should always be numeric. As the field is a free text, business can enter alphabets and special chars also. We need to list out all the invalid SSN/TINs in the report.


Any ideas would be appreciated. Thanks in advance.

Content (required):


Version (include the version you are using, if applicable):


Code Snippet (add any code snippets that support your topic, if applicable):

Tagged:

Answers

  • satya_nvs
    satya_nvs Rank 2 - Community Beginner

    The report request is based out of OTBI subject area

  • Venkat Koduvayur-Oracle
    Venkat Koduvayur-Oracle Rank 4 - Community Specialist

    Hi @satya_nvs

    You could achieve this by using regular expression in the reporting column expression to separate alphabets from string of characters.

    E.g.

    To extract numbers

    EVALUATE('REGEXP_REPLACE(%1,%2)',"Fiscal Calendar"."Accounting Period Name",'[^[:digit:]]')

    To extract alphabets

    EVALUATE('REGEXP_REPLACE(%1,%2)',"Fiscal Calendar"."Accounting Period Name",'[^[:alpha:]]')


    Pls note to replace the presentation column "Fiscal Calendar"."Accounting Period Name" with your actual column for conversion


    Hope this helps to achieve your conversion requirement.

    Regards,

    Venkat