asked on

using regexp_instr to find the number of occurences of a delimeter

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>)

ASKER CERTIFIED SOLUTION

GGuzdziol

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

neo9414

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;

SOLUTION

neo9414

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

paquicuba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dplinnane

ASKER

Great thanks both will be useful for what I want. Will split points.

dplinnane

ASKER

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;