?
Solved

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

Posted on 2013-01-10
7
Medium Priority
?
358 Views
Last Modified: 2013-01-11
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
0
Comment
Question by:rich02
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
7 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 38762976
see this code:
select REGEXP_SUBSTR('Name|Address|Telephone Number|email address'
  ,'[^|]+', 1 , 1 ) x1
, REGEXP_SUBSTR('Name|Address|Telephone Number|email address'
  ,'[^|]+', 1 , 2 ) x2
REGEXP_SUBSTR('Name|Address|Telephone Number|email address'
  ,'[^|]+', 1 , 3 ) x3
   from dual

Open in new window

0
 
LVL 7

Expert Comment

by:Javier Morales
ID: 38766187
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
0
 

Author Comment

by:rich02
ID: 38766390
Hello Javier and angelIII

Both solutions work perfectly. Very much appreciated. Thanks
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:rich02
ID: 38766491
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.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38766492
then please accept our comments and not yours :)
glad we could help
0
 
LVL 7

Expert Comment

by:Javier Morales
ID: 38766524
yes ! :)

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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question