Extract only part of data

Hello, dear Experts.
I'm  using occasionally  Oracle Business Intelligence and I've a column with number data separated by semicommas.
here is a sample row:

2859527478; 9782859527471

The number sequence has variable lenght, both before and after the semicomma.

Is there a way to filter (within OBI) these data for maintaining only the values before the semi-comma?

Thanks a lot in advance for the reply,

fabiano petroneAsked:
Who is Participating?
sdstuberConnect With a Mentor Commented:
select regexp_substr(your_column,'[^;]+') from your_table
Abhimanyu SuriSr Database EngineerCommented:
select substr('2859527478; 9782859527471',1,(instr('2859527478; 9782859527471',';')-1)) from dual


Please replace string with column name, also test it thoroughly with multiple scenarios.
fabiano petroneAuthor Commented:
excuse the delay 'cause illness
I've tried the sdstuber solution but I've the error output reported at the end of this mail.
I guess that the sdstuber  solution is right and that the query does not produce any data 'cause a wrong configuration of our Oracle Business Intelligence.
Please can you confirm that, so I can search an alternative solution to the problem?

here follows the error msg:

Odbc driver returned an error (SQLExecDirectW).
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 27042] Function regexp_substr is not defined by administrator.
Please have your System Administrator look at the log for more details on this error. (HY000)
SQL Issued: {call NQSGetQueryColumnInfo('SELECT regexp_substr("Bibliographic Details"."ISBN",''[^;]+'') FROM "Physical Items"')}
SQL Issued: SELECT regexp_substr("Bibliographic Details"."ISBN",'[^;]+') FROM "Physical Items"
sdstuberConnect With a Mentor Commented:
Looks like your BI tool is trying to parse the statement before executing it.
Try configuring it to use a "pass through" query - i.e. one where it simply sends the query as is to the database.
fabiano petroneAuthor Commented:
Thanks a lot,
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.