• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

Extract only part of data

Hello, dear Experts.
I'm  using occasionally  Oracle Business Intelligence 11.1.1.9.160119 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
0
fabiano petrone
Asked:
fabiano petrone
  • 2
  • 2
2 Solutions
 
Abhimanyu SuriSr Database EngineerCommented:
select substr('2859527478; 9782859527471',1,(instr('2859527478; 9782859527471',';')-1)) from dual

2859527478

Please replace string with column name, also test it thoroughly with multiple scenarios.
0
 
sdstuberCommented:
select regexp_substr(your_column,'[^;]+') from your_table
1
 
fabiano petroneAuthor Commented:
Hi,
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?
Thanks,
Fabiano

here follows the error msg:

***************
Error Codes: OAMP2OPY:OPR4ONWY:U9IM8TAC:OI2DL65P:OI2DL65P
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"
*************************
0
 
sdstuberCommented:
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.
1
 
fabiano petroneAuthor Commented:
Thanks a lot,
Fabiano
0
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now