INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...Want to thank those people who have made this forum such a valuable place to visit each day..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Programmers > DBMS Packages > Oracle: Oracle release 10g Forum

Parse String Based on Specific Character

Read More Threads Like This One

Bonediggler1 (TechnicalUser)

31 May 11 13:59

Hello--

I would like to parse a string based on the number of characters before and after multiple occurances of a hyphen.

A sample string is:

123456-1-12345-0

The number of characters in each portion of this string (between the hyphens) may vary. I need to be able to basically seperate:

1) All characters to the left of the 1st hyphen
2) The character between the 1st and 2nd hyphen
3) The characters between the 2nd and 3rd hyphen
4) The character after the 3rd hyphen

Thank you!!

Turkbear (TechnicalUser)

31 May 11 14:50

Hi,
are there aways 3 hyphens?

If so the using the InStr ffunction can tell you the position of each one, like
InStr(Table.stringfield,"-",1)  -- First hyphen's position
InStr(Table.stringfield,"-",2)  -- Second hyphen's position
InStr(Table.stringfield,"-",3)  -- Third hyphen's position

You should be able to use this info and combine it with Length and Substr  functions to create the new strings and then concatenate them..

To Paraphrase:"The Help you get is proportional to the Help you give.."

Bonediggler1 (TechnicalUser)

31 May 11 14:53

Thank you Turkbear...that should do the trick!

dkyrtata (Programmer)

31 May 11 15:02

I answered a similar question in November in a thread titled "Rows to columns".

The solution uses regular expressions: Here it is for your case:

CODE

SELECT
    NULLIF(REGEXP_REPLACE('-'||dash_string||'-x', '^(-)(([^-]+)-){1}(.+)$', '\\3'), dash_string) AS one,
    NULLIF(REGEXP_REPLACE('-'||dash_string||'-x', '^(-)(([^-]+)-){2}(.+)$', '\\3'), dash_string) AS two,
    NULLIF(REGEXP_REPLACE('-'||dash_string||'-x', '^(-)(([^-]+)-){3}(.+)$', '\\3'), dash_string) AS three,
    NULLIF(REGEXP_REPLACE('-'||dash_string||'-x', '^(-)(([^-]+)-){4}(.+)$', '\\3'), dash_string) AS four
  FROM(SELECT '123456-1-12345-0' dash_string FROM DUAL)

To ensure that there is always something to parse, two dummy values are added to the beginning ("-") and of the string ("-x");

The regular expression is repeated for each component being parsed with only the values of "{1}", "{2}", "{3}", and "{4} changing.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Oracle: Oracle release 10g