Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

REGEXP in SQL

Posted on 2006-07-12
5
Medium Priority
?
1,266 Views
Last Modified: 2012-08-14
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
Thanks in advance.
0
Comment
Question by:avi_ny
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 14

Expert Comment

by:sathyagiri
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

by:avi_ny
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

by:sathyagiri
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

by:avi_ny
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,
Can you explain it please.
0
 
LVL 23

Accepted Solution

by:
paquicuba earned 375 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question