Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

using regexp_instr to find the number of occurences of a delimeter

Posted on 2006-07-06
6
Medium Priority
?
801 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 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

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…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

610 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