Link to home
Start Free TrialLog in
Avatar of rich02
rich02

asked on

Oracle SQL: Selecting segments of a character string with pipe separators

Hello

I'd like to know how best to write some SQL that allows me to select a portion or portions of a character string with pipe separators. For example, the string could look like:

"Name|Address|Telephone Number|email address"

So I would like to able to say something like:

select "portion 3 from character string"
from "table name"
where "field name" is not null (--this is a constant field name where the data is stored)

Any help most welcome
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi,

In addition to angelIII solution, a simple function would solve the coding as you're requesting in your question.

create or replace function get_portion (text varchar2, 
                   portion number) return varchar2
is 
      returning_text varchar2(4000);
begin
         returning_text:=REGEXP_SUBSTR(text,'[^|]+', 1 ,portion);
         return (returning_text);
end; 
/

Open in new window


This way, the creation and coding would be as shown below.

SQL> create or replace function get_portion (text varchar2, portion number) return varchar2
  2  is 
  3        returning_text varchar2(4000);
  4  begin
  5           returning_text:=REGEXP_SUBSTR(text,'[^|]+', 1 ,portion);
  6           return (returning_text);
  7  end; 
  8  /

Function created.

SQL> select get_portion('Name|Address|Telephone Number|email address',3) from dual;

GET_PORTION('NAME|ADDRESS|TELEPHONENUMBER|EMAILADDRESS',3)
--------------------------------------------------------------------------------
Telephone Number

Open in new window


Now you can use that funcion as you wanted in your question, as part of SELECT or WHERE clauses.

select get_portion (character_string, 3)
from "table name"
where "field name" is not null;

Hope this helps,
Javier
Avatar of rich02
rich02

ASKER

Hello Javier and angelIII

Both solutions work perfectly. Very much appreciated. Thanks
Avatar of rich02

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for rich02's comment #a38766390

for the following reason:

Both solutions were very effective and easy to follow.
then please accept our comments and not yours :)
glad we could help
yes ! :)

you're welcome ! :)  glad we could help ! :)