Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

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
0
rich02
Asked:
rich02
  • 2
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Javier MoralesOracle DBACommented:
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
 
rich02Author Commented:
Hello Javier and angelIII

Both solutions work perfectly. Very much appreciated. Thanks
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
rich02Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
then please accept our comments and not yours :)
glad we could help
0
 
Javier MoralesOracle DBACommented:
yes ! :)

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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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