Solved

# REGEXP in SQL

Posted on 2006-07-12
1,248 Views
SELECT REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA, 08820 , USA', ',[^,]+,') RESULT
FROM dual;

output: , Redwood Shores,
how can i extract
1.., CA,
2.., Redwood Shores, CA
3..Redwood Shores
4..500 Oracle Parkway,
5..USA
0
Question by:avi_ny
• 2
• 2

LVL 14

Expert Comment

ID: 17091893
You could use something like
select substr('500 Oracle Parkway, Redwood Shores, CA, 08820 , USA',
INSTR('500 Oracle Parkway, Redwood Shores, CA, 08820 , USA',',',1,2),
INSTR('500 Oracle Parkway, Redwood Shores, CA, 08820 , USA',',',1,3) -
INSTR('500 Oracle Parkway, Redwood Shores, CA, 08820 , USA',',',1,2)+1) from dual

,CA,

select substr('500 Oracle Parkway, Redwood Shores, CA, 08820 , USA',
INSTR('500 Oracle Parkway, Redwood Shores, CA, 08820 , USA',',',1,1),
INSTR('500 Oracle Parkway, Redwood Shores, CA, 08820 , USA',',',1,3) -
INSTR('500 Oracle Parkway, Redwood Shores, CA, 08820 , USA',',',1,1)) from dual

Otherwise use a combination of regexp_substr and regexp_instr
0

Author Comment

ID: 17091930
Hi
Thanks,
I can do it with INSTR and SUBSTR but i want do it with REGEXP_SUBSTR.
Please can you tell me how
0

LVL 14

Expert Comment

ID: 17091983
Try this
select REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA, 08820 , USA', ',[^,]+,'1,2) RESULT
FROM dual;
select REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA, 08820 , USA', ',[^,]+,'1,1) RESULT
FROM dual;
0

Author Comment

ID: 17092075
Hi sathyagiri,
Thanks.
But why select REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA, 08820 , USA', ',[^,]+,'1,2) RESULT
FROM dual;
does not retuen ,CA, which is after second occurence of ,. why it returns ,08820,
0

LVL 23

Accepted Solution

paquicuba earned 125 total points
ID: 17092462
Just do it like this:

PAQUI@PROD > SELECT REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA, 08820 , USA','[^,]+',1,1) RESULT FROM DUAL;

RESULT
------------------
500 Oracle Parkway

Elapsed: 00:00:00.00
PAQUI@PROD > SELECT REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA, 08820 , USA','[^,]+',1,2) RESULT FROM DUAL;

RESULT
---------------
Redwood Shores

Elapsed: 00:00:00.01
PAQUI@PROD > SELECT REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA, 08820 , USA','[^,]+',1,3) RESULT FROM DUAL;

RES
---
CA

Elapsed: 00:00:00.01
PAQUI@PROD > SELECT REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA, 08820 , USA','[^,]+',1,4) RESULT FROM DUAL;

RESULT
-------
08820

Elapsed: 00:00:00.00
PAQUI@PROD > SELECT REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA, 08820 , USA','[^,]+',1,5) RESULT FROM DUAL;

RESU
----
USA

Elapsed: 00:00:00.03
PAQUI@PROD > SELECT REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA, 08820 , USA',',[^,]+,[^,]+')RESULT FROM DUAL;

RESULT
--------------------
, Redwood Shores, CA

Elapsed: 00:00:00.00
0

## Featured Post

### Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.