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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hello Javier and angelIII
Both solutions work perfectly. Very much appreciated. Thanks
Both solutions work perfectly. Very much appreciated. Thanks
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.
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
glad we could help
yes ! :)
you're welcome ! :) glad we could help ! :)
you're welcome ! :) glad we could help ! :)
In addition to angelIII solution, a simple function would solve the coding as you're requesting in your question.
Open in new window
This way, the creation and coding would be as shown below.
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