Solved

using regexp_instr to find the number of occurences of a delimeter

Posted on 2006-07-06
6
797 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
[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
6 Comments
 
LVL 14

Accepted Solution

by:
GGuzdziol earned 100 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 100 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Assisted Solution

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

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…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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 setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

705 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