I'm trying to split a string with regexp_subtr, but i can't make it work.
So, first, i have this query
select regexp_substr('Helloworld - test!' ,'[[:space:]]-[[:space:]]') from dual
which very nicely extracts my delimiter - blank-blank
But then, when i try to split the string with this option, it just doesn't work.
select regexp_substr('Helloworld - test!' ,'[^[[:space:]]-[[:space:]]]+')from dual
The query returns nothing.
Help will be much appreciated! Thanks
Trying to negate the match string
'[[:space:]]-[[:space:]]'
by putting it in a character class with a circumflex (^) to negate it will not work. Everything between a pair of square brackets is treated as a list of optional single characters except for named named character classes which expand out to a list of optional characters, however, due to the way character classes nest, it's very likely that your outer brackets are being interpreted as follows:[^[[:space:]]
A single non space non left square bracket character-
followed by a single hyphen[[:space:]]
followed by a single space character]+
followed by 1 or more closing square brackets.It may be easier to convert your multi-character separator to a single character with regexp_replace, then use regex_substr to find you individual pieces:
In this code I first changed
-
tochr(11)
. That's the ASCII vertical tab (VT) character which is unlikely to appear in most text strings. Then the match expression of the regexp_substr matches all non VT characters followed by either a VT character or the end of line. Only the non VT characters are returned (the first subexpression).If i understood correctly, this will help you. Currently you are getting output as
Helloworld
(with space at the end). So i assume u don't want to have space at the end. If so you can simply use the space in the delimiter also like.As u mentioned in ur comment if u want two columns output with
Helloworld
andtest!
. you can do the following.Slight improvement on MT0's answer. Dynamic count using regexp_count and proves it handles nulls where the format of [^delimiter]+ as a pattern does NOT handle NULL list elements. More info on that here: Split comma seperated values to columns
SQL Fiddle
Oracle 11g R2 Schema Setup:
Query 1:
Results: