[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

using regexp_instr to find the number of occurences of a delimeter

Posted on 2006-07-06
6
Medium Priority
?
802 Views
Last Modified: 2008-02-01
SELECT
  REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA', '[^,]+', 1, 2) "REGEXP_INSTR"
  FROM DUAL;

the above gets the position of the second comma

How would I modify the above to find the number of occurences of a comma. Not sure if this is the way to go. Any sugestions appreciated.

Thanks in advance.
REGEXP_INSTR(<source_string>, <pattern>, <start_position>, <occurrence>, <return_option>, <match_parameter>)
0
Comment
Question by:dplinnane
6 Comments
 
LVL 14

Accepted Solution

by:
GGuzdziol earned 400 total points
ID: 17048933
select length('500 Oracle Parkway, Redwood Shores, CA') - length(replace('500 Oracle Parkway, Redwood Shores, CA', ',', '')) occurences
  from dual;
0
 
LVL 9

Expert Comment

by:neo9414
ID: 17048976
or you can use translate function

select length('500 Oracle Parkway, Redwood Shores, CA') - length(translate('500 Oracle Parkway, Redwood Shores, CA', 'A,', 'A')) occurences  from dual;
0
 
LVL 9

Assisted Solution

by:neo9414
neo9414 earned 400 total points
ID: 17048990
The translate function will be useful in cases for finding occurences of multiple character e.g.: 'space' and 'comma'

To find occurence of comma

SQL>select length('500 Oracle Parkway, Redwood Shores, CA') - length(translate('500 Oracle Parkway, Redwood Shores, CA', 'A,', 'A')) occurences from dual;

OCCURENCES
----------
         2

To find occerence of 'comma' and 'space'

SQL>select length('500 Oracle Parkway, Redwood Shores, CA') - length(translate('500 Oracle Parkway, Redwood Shores, CA', 'A, ', 'A')) occurences  from dual;

OCCURENCES
----------
         7

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 23

Assisted Solution

by:paquicuba
paquicuba earned 200 total points
ID: 17050453
If you wish to use regular expressions, then you need to use REGEXP_REPLACE rather:

SELECT LENGTH(REGEXP_REPLACE('500 Oracle Parkway, Redwood Shores, CA','[^,]','')) FROM DUAL;
0
 

Author Comment

by:dplinnane
ID: 17050477
Great thanks both will be useful for what I want. Will split points.
0
 

Author Comment

by:dplinnane
ID: 17050576
This what I came up with slight modification to account for length of delimiter, divide by the length of delim. Probably faster then translate, my string will be up to 1.5mb

DECLARE
n_count  varchar2(1000) :=0;
PIN_PRICE_EVENT_MSG CLOB;
pin_delim varchar2(10) := '||';
BEGIN
PIN_PRICE_EVENT_MSG := '<?xml version="1.0"?><PDATA><HDR>200607|||03190133~RMS</HDR><EVH>31~00006~204745175~P~Y~N~20060704|||000100~20060712235900~Prom 1 - Laks</EVH><EVD>204745175~04608||437~14.99~A~A</EVD><EVH>18~00006~204745175~P~Y~N~20060704000100~20060712235900~Prom 1 - Laks</EVH><EVD>18-204745175~04608455~8.99~A~A</EVD><EVH>32~00006~204745176~P~Y~N~20060704000100~20060712235900~Prm 2 - Laks</EVH><EVD>204745176~04622331~14.49~A~S</EVD><EVH>31~00006~204745176~P~Y~N~20060704000100~20060712235900~Prm 2 - Laks</EVH><EVD>204745176~04622439~13.49~A~S</EVD><EVH>31~00010~204745175~P~Y~N~20060704000100~20060712235900~Prom 1 - Laks</EVH><EVD>204745175~04608437~14.99~A~A</EVD><EVH>31~00010~204745175~P~Y~N~20060704000100~20060712235900~Prom 1 - Laks</EVH><EVD>204745175~04608455~8.99~A~A</EVD><EVH>31~00010~204745176~P~Y~N~20060704000100~20060712235900~Prm 2 - Laks</EVH><EVD>204745176~04622331~14.49~A~S</EVD><EVH>31~00010~204745176~P~Y~N~20060704000100~20060712235900~Prm 2 - Laks</EVH><EVD>204745176~04622439~13.49~A~S</EVD></PDATA>';
 
select (length(PIN_PRICE_EVENT_MSG) - length(replace(PIN_PRICE_EVENT_MSG, pin_delim, '')))/length(pin_delim) occurences into n_count
from dual;
 
DBMS_OUTPUT.put_line ('n_count 2 :' || n_count);
     
END;
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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 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.
Suggested Courses

872 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