Parse String Based on Specific Character 1

[Bonediggler1]

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]

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]

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

 

[dkyrtata]

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:

 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.