Solved

using regexp_instr to find the number of occurences of a delimeter

Posted on 2006-07-06
6
790 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 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help converting some sql server sql to Oracle sql 9 72
data lookup in Oracle - need suggestions 55 103
join 2 views with 5 conditions 3 45
help on oracle query 5 33
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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now