Oracle Fusion AI Data Platform Forum

Home› Oracle Analytics and AI› Oracle Analytics and AI Forums› Oracle Fusion AI Data Platform Forum

Categories

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

extract substring into new column FDI

Received Response
25
Views
2
Comments
Raluca Balan-Oracle
Raluca Balan-Oracle Rank 2 - Community Beginner
in Oracle Fusion AI Data Platform Forum

Hi! I have this FDI subject area and was trying different types of SQL functions with not much success.

Can you please help with this issue? I would like to extract from Location table

  1. only the Country name (AUSTRALIA, CHINA, FRANCE etc) into new column
  2. only the City Names (Beijing, Paris etc) into 2nd new column

How can I achieve in FDI environment?

Location

AUSTRALIA-NSW-BATHURST-

AUSTRALIA-NSW-CHULLORA-

AUSTRALIA-NSW-PORT BOTANY-

AUSTRALIA-QLD-NEBO-

CHINA--BEIJING-

CHINA-BEIJING-BEIJING-

FRANCE--LILLE-

FRANCE--MARSEILLE-

FRANCE--PARIS-

FRANCE-LIAONING-LILLE-

FRANCE-NONE-BORDEAUX-

FRANCE-NONE-PARIS-

FRANCE-NONE-PARIS-1

FRANCE-NONE-PARIS-2

FRANCE-NONE-PARIS-MANUFACTURING

FRANCE-NONE-PARIS-OFFICE

GERMANY-BE-BERLIN-

GERMANY-BW-STUTTGART-

GERMANY-BY-MÜNCHEN-

GERMANY-HE-FRANKFURT(M)-

GERMANY-HE-WIESBADEN-

GERMANY-HH-HAMBURG-

GERMANY-NW-DÜSSELDORF -

GERMANY-SN-DRESDEN-

INDIA-BRISTOL-BRISTOL-1

INDIA-DELHI-NEW DELHI-

INDIA-DELHI-NEW DELHI-1

Tagged:

Answers

  • philipgodfrey
    philipgodfrey Rank 6 - Analytics & AI Lead

    Hi @Raluca Balan-Oracle

    I can't say for sure if this would work in FDI, but something like the below provides what you have requested in Oracle SQL.

    select 'AUSTRALIA-NSW-BATHURST-' as full_location,
    REGEXP_SUBSTR('AUSTRALIA-NSW-BATHURST-', '^[^-]+') as first_location,
    REGEXP_SUBSTR(RTRIM('AUSTRALIA-NSW-BATHURST-', '-'), '[^-]+$') as second_location
    from dual;

    Returns:

    image.png

    Please let me know if something like this will help :)

    Thanks

    Phil

  • RVohra
    RVohra Mod

    @Raluca Balan-Oracle , Welcome to the Oracle Analytics Community!

    We might need to explore using LEFT, RIGHT, POSITION, on very high level see if something below works

    LEFT(
    RIGHT(
    "Location",
    LENGTH("Location") - POSITION('-' IN "Location") - 1
    ),
    POSITION(
    '-' IN
    RIGHT(
    "Location",
    LENGTH("Location") - POSITION('-' IN "Location") - 1
    )
    ) - 1
    )

    Hope it helps!