Solved

REGEXP in SQL

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.

932 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now