Oracle Transactional Business Intelligence

Products Banner

Finding the alphabets from a Alphanumeric in OTBI.

Received Response

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):


  • The report request is based out of OTBI subject area

  • Hi @satya_nvs

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


    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.