extract substring into new column FDI

Question

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

* only the Country name (AUSTRALIA, CHINA, FRANCE etc) into new column
* 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

RVohra · Answer

@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!

philipgodfrey · Answer

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:

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

Thanks

Phil