Home » SQL & PL/SQL » SQL & PL/SQL » Word count (9.2.0.5)
Word count [message #362243] Mon, 01 December 2008 07:49 Go to next message
avik2009
Messages: 61
Registered: November 2008
Member
Find number of words from the column data by passing comma separated string. e.g, If I pass a string like
'ABOUT,ETC,ABOVE' Count should be 4

Column
------
ABOUT
1234
1-2
THOMAS
ABOVE
ABOVE
ETC
GEM
DOCUMENT


Re: Word count [message #362247 is a reply to message #362243] Mon, 01 December 2008 07:56 Go to previous messageGo to next message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
Quote:
If I pass a string like 'ABOUT,ETC,ABOVE' Count should be 4


These are three words by the way!!

Find the number of commas in the string and increment it by 1 if the last word is not followed by a comma.

Rajy
Re: Word count [message #362248 is a reply to message #362243] Mon, 01 December 2008 08:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also always post a test case: create table and insert statements along with the result you want with these data.

What is the relation between:
'ABOUT,ETC,ABOVE'
and
Quote:
Column
------
ABOUT
1234
1-2
THOMAS
ABOVE
ABOVE
ETC
GEM
DOCUMENT


Regards
Michel
Re: Word count [message #362257 is a reply to message #362248] Mon, 01 December 2008 09:04 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
I am passing a string like 'ABOUT,ETC,ABOVE'...I want to search
WORDS separated by delimeter and then return count of words in the table

Create table t ( wrd varchar2(10));

Insert into t values('ABOUT');
Insert into t values('ABOVE');
Insert into t values('ETC');
Insert into t values('ABOVE');
Insert into t values('DICTIONARY');
Insert into t values('1-2');


If I pass a string separated by ',' like 'ETC,ABOVE' the word count should be returned as 3 as ABOVE occured twice in column wrd.

[Updated on: Mon, 01 December 2008 10:05] by Moderator

Report message to a moderator

Re: Word count [message #362268 is a reply to message #362257] Mon, 01 December 2008 10:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still don't understand the relation between the string and the table.
What does the table has to do with the question?

By the way, the question on the number of words/elements in the string has been answered.

Regards
Michel
Re: Word count [message #362269 is a reply to message #362243] Mon, 01 December 2008 11:13 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Seems you need the method described e.g. in this thread on AskTom: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:210612357425.
Have a look at the in_list function.
Re: Word count [message #362274 is a reply to message #362257] Mon, 01 December 2008 11:59 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@avik2009,

I will break the solution into three steps:
Step-1: Your first step would be to split the words delimitted by comma. You can do by the following code:
SQL> SELECT SUBSTR (col_1, start_l + 1, end_l - start_l - 1) col_2
  2    FROM (SELECT     col_1, LEVEL l1,
  3                     INSTR (col_1,
  4                            ',',
  5                            LEVEL - 1,
  6                            (CASE
  7                                WHEN LEVEL - 1 = 0
  8                                   THEN 1
  9                                WHEN LEVEL - 1 < 0
 10                                   THEN ABS (LEVEL - 1)
 11                                ELSE LEVEL - 1
 12                             END
 13                            )
 14                           ) start_l,
 15                     CASE INSTR (col_1, ',', 1, LEVEL)
 16                        WHEN 0
 17                           THEN LENGTH (col_1) + 1
 18                        ELSE INSTR (col_1, ',', 1, LEVEL)
 19                     END end_l
 20                FROM (WITH test_tab_new AS
 21                           (SELECT 'ABOUT,ETC,ABOVE' col_1
 22                              FROM DUAL)
 23                      SELECT col_1
 24                        FROM test_tab_new)
 25          CONNECT BY LEVEL <=
 26                            LENGTH (col_1) - LENGTH (REPLACE (col_1, ',', ''))
 27                            + 1);

COL_2
---------------
ABOUT
ETC
ABOVE

3 rows selected.



I think there is a post by Kevin Meade or SnippetyJoe about this. I am not sure. You should search in the forum for better codes.

Step-2: Anyways after splitting the data you have to join(inner join) the result with your table.

Step-3: Do a COUNT(*) on the resultset of the query in Step-2 and you will get the required result.

Hope this helps.
Regards,
Jo
Re: Word count [message #362275 is a reply to message #362274] Mon, 01 December 2008 12:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't need to split to just count the commas.

Regards
Michel
Re: Word count [message #362277 is a reply to message #362275] Mon, 01 December 2008 12:11 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Michel,

I don't think OP wants to count the commas. Let me explain what I understood.

Consider he has a data something like:
SQL> SELECT * from test_tab;

COL_1
----------
ABOVE
ETC
Dictionary
ABOUT
ETC
1-2
ABOUT
ABOVE

8 rows selected.


and his search criteria is 'ABOUT,ETC,ABOVE'. i.e. he wants to find out how many times each word delimitted by comma occurs in the table data(the above table here). i.e. SUM of COUNTS of how many time the word 'ABOUT', 'ETC' and 'ABOVE' occurs.
So i think his expected output (based on the above sample table) will be:
ABOVE 2 times
ETC 2 times
ABOUT 2 times

COUNT(*)
--------
       6


Hope I didn't misread OP's requirement.

Regards,
Jo

[Updated on: Mon, 01 December 2008 12:13]

Report message to a moderator

Re: Word count [message #362278 is a reply to message #362274] Mon, 01 December 2008 12:20 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Try using array

[Updated on: Mon, 01 December 2008 12:23]

Report message to a moderator

Re: Word count [message #362279 is a reply to message #362277] Mon, 01 December 2008 12:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
don't think OP wants to count the commas.

Yes he wants:
Quote:
Find number of words from the column data by passing comma separated string

Which is just the number of commas + 1 as Rajy said in the first (and for me definitive answer).

Quote:
Consider he has a data something like:

And then? I still don't what this has to do with the question.

Quote:
and his search criteria is 'ABOUT,ETC,ABOVE'. i.e. he wants to find out how many times each word delimitted by comma occurs in the table data...

Wow! you're a great marabout if this is a real question.

By the way, this has already been asked so many times, just a classical "varying inlist".
Also you seem to like to complexify the query as most as you can:
 20                FROM (WITH test_tab_new AS
 21                           (SELECT 'ABOUT,ETC,ABOVE' col_1
 22                              FROM DUAL)
 23                      SELECT col_1
 24                        FROM test_tab_new)

Why not just:
FROM (SELECT 'ABOUT,ETC,ABOVE' col_1 FROM DUAL)


Regards
Michel

[Updated on: Mon, 01 December 2008 12:26]

Report message to a moderator

Re: Word count [message #362284 is a reply to message #362243] Mon, 01 December 2008 12:41 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
Hey Guys, really sorry if my question mislead you.

John was right

Quote:

search criteria is 'ABOUT,ETC,ABOVE'. i.e. wants to find out how many times each word delimitted by comma occurs in the table data...





Re: Word count [message #362286 is a reply to message #362257] Mon, 01 December 2008 12:46 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Michel,

Quote:

Wow! you're a great marabout if this is a real question.


Thanks a lot for the "remark".
Well, You can be a marabout too if you closely read the following:

avik2009 wrote on Mon, 01 December 2008 20:34
I am passing a string like 'ABOUT,ETC,ABOVE'...I want to search
WORDS separated by delimeter and then return count of words in the table
If I pass a string separated by ',' like 'ETC,ABOVE' the word count should be returned as 3 as ABOVE occured twice in column wrd.



Quote:

Also you seem to like to complexify the query as most as you can:


Thanks for pointing out so that OP can correct it. I was trying something different when I took that particular code. I never mentioned my code is perfect now did I? I did ask OP to search for better codes:
Quote:

You should search in the forum for better codes.



Regards,
Jo



Re: Word count [message #362288 is a reply to message #362284] Mon, 01 December 2008 12:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quoting a sentence than most does not understand does not explain it, not at all.
You should better state it clearly. I asked twice what the table has to do with the question, you didn't answer.
Rajy gave an answer and you said nothing leading us to think it is in the right way.
I think you need to enhance the way you explain your question and post.

Regards
Michel
Re: Word count [message #362297 is a reply to message #362286] Mon, 01 December 2008 13:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
joicejohn wrote on Mon, 01 December 2008 19:46
Quote:

Also you seem to like to complexify the query as most as you can:


Thanks for pointing out so that OP can correct it. I was trying something different when I took that particular code. I never mentioned my code is perfect now did I? I did ask OP to search for better codes:
Quote:

You should search in the forum for better codes.



And then? Just because you didn't say it is the best one or you warn OP to search for a better one we can't criticize your query?
Actually, it is so rare to see a factoring clause deep inside a query that it is worth to wonder why. If you'd put it outside the main query I didn't even notice it. But how had you the idea to put this? I'm interesting in this, I always learn much knowing how such constructions come in a query.

Regards
Michel

Re: Word count [message #362325 is a reply to message #362243] Mon, 01 December 2008 15:39 Go to previous messageGo to next message
knicely87
Messages: 25
Registered: December 2008
Location: Pittsburgh, PA
Junior Member
Why wouldn't a simple INSTR function work?

SQL> SELECT wrd FROM t;

WRD
----------
ABOUT
1234
1-2
THOMAS
ABOVE
ABOVE
ETC
GEM
DOCUMENT

9 rows selected.

SQL> SELECT COUNT(wrd) FROM t WHERE INSTR('ABOUT,ETC,ABOVE' || ',', wrd || ',') > 0;

COUNT(WRD)
----------
4

******

SQL> SELECT wrd FROM t;

WRD
----------
ABOUT
ABOUT
ABOVE
ETC
ABOVE
DICTIONARY
1-2

7 rows selected.

SQL> SELECT COUNT(wrd) FROM t WHERE INSTR('ETC,ABOVE' || ',', wrd || ',') > 0;

COUNT(WRD)
----------
3

******

SQL> SELECT wrd FROM t;

WRD
----------
ABOVE
ETC
Dictionary
ABOUT
ETC
1-2
ABOUT
ABOVE

8 rows selected.

SQL>
SQL> SELECT COUNT(wrd) FROM t WHERE INSTR('ABOUT,ETC,ABOVE' || ',', wrd || ',') > 0;

COUNT(WRD)
----------
6
Re: Word count [message #362326 is a reply to message #362297] Mon, 01 December 2008 15:44 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Michel,

Criticize how much ever you want. If I had any fear for your criticizm I wouldn't have posted the query. (I won't learn to fly if I am afraid to fall right?) The usage of WITH Clause was not an intentional one here. I told you I was trying out something else when I stumbled upon this post.

Quote:

Thanks for pointing out so that OP can correct it. I was trying something different when I took that particular code.


I don't know how you took this but I meant THANKS for correcting me by pointing out this mistake and nothing more.

Good Day!!!
[***Added***]

Good and simple logic @knicely87. Indeed I was making things complicated. Thanks...

Regards,
Jo

[Updated on: Mon, 01 December 2008 15:49]

Report message to a moderator

Re: Word count [message #362359 is a reply to message #362325] Tue, 02 December 2008 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
knicely87, welcome on the forum.

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) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Word count [message #362516 is a reply to message #362359] Tue, 02 December 2008 09:49 Go to previous message
knicely87
Messages: 25
Registered: December 2008
Location: Pittsburgh, PA
Junior Member
Thanks!
Previous Topic: Is it possible with a Trigger?
Next Topic: external table
Goto Forum:
  


Current Time: Fri Dec 09 21:35:56 CST 2016

Total time taken to generate the page: 0.09116 seconds