CODEWITHSUNDEEP
sqloracle-database
Anson Wong
Anson Wong

Reputation: 31

How to split the words in oracle using regexp_substr?

I have a string that containing 3 parts, the whole string is:

`aUNITED STATES :bUSCOMPANY,c7/1990.`

How can I get the 3 parts without the lower case character separately by using regexp_substr in oracle?

The result should be:

UNITED STATES :
USCOMPANY,
7/1990.

I have tried the following code.

select REGEXP_SUBSTR('aUNITED STATES :bUSCOMPANY,c7/1990.', '[^a-z]+') 
from dual;

I can successfully get UNITED STATES :, but how can I get other results?

Upvotes: 3

Views: 1115

Answers (3)

David דודו Markovitz
David דודו Markovitz

Reputation: 44991

select  regexp_substr ('aUNITED STATES :bUSCOMPANY,c7/1990.','^a([^b]*)',1,1,'c',1)
       ,regexp_substr ('aUNITED STATES :bUSCOMPANY,c7/1990.','b([^c]*)' ,1,1,'c',1)
       ,regexp_substr ('aUNITED STATES :bUSCOMPANY,c7/1990.','c(.*)$'   ,1,1,'c',1)

from    dual
;

or

select  regexp_substr ('aUNITED STATES :bUSCOMPANY,c7/1990.','[a-c]([^a-c]*)',1,1,'c',1)
       ,regexp_substr ('aUNITED STATES :bUSCOMPANY,c7/1990.','[a-c]([^a-c]*)',1,2,'c',1)
       ,regexp_substr ('aUNITED STATES :bUSCOMPANY,c7/1990.','[a-c]([^a-c]*)',1,3,'c',1)

from    dual
;

or

select  regexp_substr ('aUNITED STATES :bUSCOMPANY,c7/1990.','[^a-c]*',1,2)
       ,regexp_substr ('aUNITED STATES :bUSCOMPANY,c7/1990.','[^a-c]*',1,4)
       ,regexp_substr ('aUNITED STATES :bUSCOMPANY,c7/1990.','[^a-c]*',1,6)

from    dual
;

or

select  regexp_substr ('aUNITED STATES :bUSCOMPANY,c7/1990.','^a(.*?)b',1,1,'c',1)
       ,regexp_substr ('aUNITED STATES :bUSCOMPANY,c7/1990.','b(.*?)c' ,1,1,'c',1)
       ,regexp_substr ('aUNITED STATES :bUSCOMPANY,c7/1990.','c(.*)$'  ,1,1,'c',1)

from    dual
;

Upvotes: 1

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6366

1) Fisrt option. With regexp. 

select regexp_substr('aUNITED STATES :bUSCOMPANY,c7/1990.','[a-z](.*)[a-z](.*)[a-z](.*)',1,1,null,1) 
     , regexp_substr('aUNITED STATES :bUSCOMPANY,c7/1990.','[a-z](.*)[a-z](.*)[a-z](.*)',1,1,null,2)
     , regexp_substr('aUNITED STATES :bUSCOMPANY,c7/1990.','[a-z](.*)[a-z](.*)[a-z](.*)',1,1,null,3)  
  from dual;

regexp_substr allow to extract regexp group from string regexp_substr - desc. The group is define by brackets "(.......)".

2) xquery/xmltable

 select xmlcast(c.column_value as varchar2(200)) from xmltable('for $i in ora:tokenize($str,"[a-z]")
                                               where string-length($i) >0
                                                return $i' passing 'aUNITED STATES :bUSCOMPANY,c7/1990.' as "str" ) c;

ora:tokenize($str,"[a-z]") - split string into string sequnect using reg_exp pattern. Syntax inside xmltable is called "FLWOR Expressions"

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

You can do it with a combination of substr and instr, as the string pattern is consistent.

select 
--replace col with the column name from the table
substr(col,2,instr(col,':')-1) part_1,
substr(col,instr(col,':')+2,instr(col,',')-instr(col,':')-1) part_2,
substr(col,instr(col,',')+1) part_3
from yourtable

Upvotes: 0

Related Questions