Solved

REGEXP in SQL

Posted on 2006-07-12
5
1,256 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
  • 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 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

832 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