Home » SQL & PL/SQL » SQL & PL/SQL » how to count comma seprated values (Oracle10g)
how to count comma seprated values [message #390624] Mon, 09 March 2009 00:18 Go to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

hi,
my function retruns these values ina sinlge column containg comma seprated values
(5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48)
I have to dispaly total count return by this funtion how to dispaly this?
Re: how to count comma seprated values [message #390625 is a reply to message #390624] Mon, 09 March 2009 00:36 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
sr_orcl,


my function retruns these values ina sinlge column containg comma seprated values
(5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48)
I have to dispaly total count return by this funtion how to dispaly this? 


Post your function.


Thanks and Regards,
Hammer
Re: how to count comma seprated values [message #390626 is a reply to message #390624] Mon, 09 March 2009 00:37 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: how to count comma seprated values [message #390628 is a reply to message #390624] Mon, 09 March 2009 00:41 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@sr_orcl,

1. Take Length of the desired string.
2. Take the Length of the string after removing the commas.
3. Step 1 - Step 2
4. Add 1 to the result and you will get the output.

LENGTH and REPLACE Functions might come in handy here for you.

Regards,
Jo

[Updated on: Mon, 09 March 2009 00:42]

Report message to a moderator

Re: how to count comma seprated values [message #390630 is a reply to message #390624] Mon, 09 March 2009 00:58 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

@sr_orcl,

What have you tried so far ???

Joicejohn has made this easy for you,post whatever you have tried and we can improve or correct.

Regards,
Ashoka BL
Bengaluru
Re: how to count comma seprated values [message #390743 is a reply to message #390630] Mon, 09 March 2009 09:47 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
I think sr_orcl is trying count values separated by comman, I hope this can help.

SQL> WITH TEMPDATA AS
     (SELECT '1,2,3,4,5,6,7,8,9,10' STR
        FROM DUAL)
SELECT COUNT (COMMA_TO_COL)
  FROM (SELECT REGEXP_SUBSTR (STR, '[^,]+', 1
                             ,ROW_NUMBER () OVER (ORDER BY NULL)) COMMA_TO_COL
          FROM (SELECT     REGEXP_INSTR (STR, '[^,]+', 1, LEVEL) START_POS, STR
                      FROM TEMPDATA
                CONNECT BY LEVEL <= LENGTH (STR))
         WHERE START_POS <> 0)

COUNT(COMMA_TO_COL)
-------------------
                 10

1 row selected.


You can also use REGEXP_COUNT in Oracle 11g, which at hide the complexity of the query.

Thanks
Trivendra

[Updated on: Mon, 09 March 2009 09:48]

Report message to a moderator

Re: how to count comma seprated values [message #390749 is a reply to message #390743] Mon, 09 March 2009 10:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
10/10 for gloriously over complex solutions - the only way I can think of that would top that would be to change the commas into XML close and open tags, use XMLSEQUENCE to convert it into a table, and then count the number of rows in the table - and I'm not convinced that your's isn't more complex than that!.

I'd do it the way joicejohn suggested:
WITH TEMPDATA AS (SELECT '1,2,3,4,5,6,7,8,9,10' STR FROM DUAL)
select length(str) - length(replace(str,',',''))+1
from   tempdata;


Not than I'[m bored at the moment you understand, but here's the XML approach:
WITH TEMPDATA AS (SELECT '1,2,3,4,5,6,7,8,9,10' STR FROM DUAL)
select count(*)
from   (select xmltype('<A><B>'||replace(str,',','</B><B>')||'</B></A>') x
        from   tempdata) src
        ,table(xmlsequence(extract(src.x,'A/B')))
        ;
Re: how to count comma seprated values [message #390750 is a reply to message #390624] Mon, 09 March 2009 10:49 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

is that much complicated or my answer is wrong,

SELECT (  LENGTH ('1,2,3,4,5,6,7,8,9,10,11,12,13,100,200,300,400,10000,200000')
        - LENGTH
             (REPLACE
                 ('1,2,3,4,5,6,7,8,9,10,11,12,13,100,200,300,400,10000,200000',
                  ',',
                  NULL
                 )
             )
        + 1
       )
  FROM DUAL


Regards,
Ashoka BL
Re: how to count comma seprated values [message #390752 is a reply to message #390750] Mon, 09 March 2009 11:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, you're right, it is a classical very old problem and it would be better to let OP find it by himself.

Regards
Michel
Re: how to count comma seprated values [message #390760 is a reply to message #390624] Mon, 09 March 2009 11:26 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Yeah,even i was waiting for OP to respond,but saw some complicated approaches, so thought of posting my answer.

Regards,
Ashoka BL
Bengaluru
Re: how to count comma seprated values [message #390824 is a reply to message #390760] Mon, 09 March 2009 23:40 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
I really don't mean about my query (about the complexity), I really want to share the complexity of the given query with respect of REGEXP_COUNT function in Oracle 11g as it can be eaisly done by REGEXP_COUNT.

Razz

Thanks
Trivendra
Re: how to count comma seprated values [message #390838 is a reply to message #390824] Tue, 10 March 2009 00:04 Go to previous messageGo to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

Hi Thanks To all for giving me lots of suggestions.

I wrote this function that returns total number of count as well as it accept comma separated values.


CREATE OR REPLACE FUNCTION Fn_Receipts_status1(p_Empid NUMBER,
p_RcptBkcd NUMBER,
p_RcptBkid NUMBER,
p_rcptstatus NUMBER,
p_companyid NUMBER) --Added for multi company




RETURN VARCHAR2 IS
lt_Tabtocom VARCHAR2(4000);
ln_minrcptno NUMBER;
ln_maxrcptno NUMBER;
ln_cnt NUMBER;
ln_Idxcnt NUMBER;
lt_outtab DBMS_UTILITY.UNCL_ARRAY;

CURSOR c_rcptno(cn_minrcptno NUMBER, cn_maxrcptno NUMBER) IS
SELECT rd.rcptnumber
FROM RCPTBOOK_DTL rd, RCPTBOOK_HDR rh
WHERE rd.allocempid = p_empid
AND rd.rcptbookid = rh.rcptbookid
AND rh.cg_rcptcode = NVL(p_RcptBkcd, rh.cg_rcptcode)
AND rd.cg_rcptstatus = p_rcptstatus
AND rd.rcptnumber BETWEEN cn_minrcptno AND cn_maxrcptno
AND rd.status <> 'X'
AND rh.status <> 'X'
AND rh.companyid = p_companyid; --Added for multi company
BEGIN
DBMS_OUTPUT.disable;
ln_Idxcnt := 0;
SELECT MIN(r.rcptnumber), MAX(r.rcptnumber)
INTO ln_minrcptno, ln_maxrcptno
FROM rcptbook_dtl r
WHERE r.allocempid = p_empid
AND r.rcptbookid = p_RcptBkid
AND r.cg_rcptstatus = p_rcptstatus
AND r.status <> 'X';
FOR rcpts IN c_rcptno(ln_minrcptno, ln_maxrcptno) LOOP
ln_Idxcnt := ln_Idxcnt + 1;
lt_outtab(ln_Idxcnt) := rcpts.rcptnumber;
END LOOP;
DBMS_UTILITY.TABLE_TO_COMMA(lt_outtab, ln_cnt, lt_Tabtocom);
DBMS_OUTPUT.PUT_LINE(lt_Tabtocom);
FOR rcpts IN 1 .. ln_Idxcnt LOOP
lt_outtab(rcpts) := NULL;
END LOOP;
RETURN(ln_cnt);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
RETURN NULL;
END Fn_Receipts_status1;
Re: how to count comma seprated values [message #390840 is a reply to message #390624] Tue, 10 March 2009 00:04 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: how to count comma seprated values [message #390841 is a reply to message #390838] Tue, 10 March 2009 00:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:
Michel Cadot wrote on Fri, 06 March 2009 10:59
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

WHEN OTHERS is just stupid.

Regards
Michel


Re: how to count comma seprated values [message #390842 is a reply to message #390624] Tue, 10 March 2009 00:10 Go to previous messageGo to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

Sorry I am not followoing the posting guide lines,

Please check in the given attached file for DDL scripts.
  • Attachment: t1_ddl.sql
    (Size: 1.89KB, Downloaded 179 times)
Re: how to count comma seprated values [message #390844 is a reply to message #390624] Tue, 10 March 2009 00:15 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
>my function retruns these values ina sinlge column containg comma seprated values
This is a seriously FLAWED "design"/implementation.
Please learn & use Third Normal Form.
Re: how to count comma seprated values [message #390894 is a reply to message #390838] Tue, 10 March 2009 03:49 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I don't think you've read up about what Dbms_utility.Comma_To_Table really does.

It takes a comma separated list of valid oracle object names, and returns a collection containing those names.
If you pass it an invalid name, it raises an exception.
So, if one of your strings is > 30 chrs, or contains a character other than $,_, or starts with a number, then your code will fail.

Previous Topic: Dynamic sql cursor into pipe row
Next Topic: how to use migrate script
Goto Forum:
  


Current Time: Tue Dec 06 00:06:42 CST 2016

Total time taken to generate the page: 0.09154 seconds