?
Solved

REGEXP in SQL

Posted on 2006-07-12
5
Medium Priority
?
1,265 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

800 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