Finding all instances of a word in a string with SQL

Question

I want to search a column for all instances of a string

For example, if I have this a column with the value all good all bad all better I want to find the number of instances of all.

I tried

select instr(column, 'all') from test_table;

but that only returns the position of the first occurrence of all. I'm using Oracle 11g database.

Is there a way I can find all instances of a specific string in Oracle db?

first thing i thought of was the CHARINDEX function, but then I saw Oracle. I have only been working in Oracle for about a month now, but this might help you. http://nishantrana.wordpress.com/2007/10/03/using-left-and-charindex-in-oracle/ — mmeasor, May 01 '14 at 20:31

wvdz · Answer 1 · 2014-05-01T20:58:46.457

2

SELECT REGEXP_COUNT(column, 'all') AS Cnt
FROM test_table;

edited May 01 '14 at 20:58

answered May 01 '14 at 20:33

wvdz

Oracle has a regexp_count function (http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions135.htm#SQLRF20014). If you're going to use a regular expression, you should be using that. – Justin Cave May 01 '14 at 20:51

score 2 · Accepted Answer · answered May 01 '14 at 20:57

Since you're on 11g, you can use the regexp_count function. If you want to do a case-sensitive count

SELECT regexp_count( column, 'all' )
  FROM table_name

If you want a case-insensitive count

SELECT regexp_count( column, 'all', 1, 'i' )
  FROM table_name

score 0 · Answer 3 · answered May 01 '14 at 20:52

0

This works with MySQL - have not tested with Oracle but I see no reason why it wouldn't work:

SELECT (LENGTH(column) - LENGTH(REPLACE(column, 'all', '')))/LENGTH('all') FROM table

answered May 01 '14 at 20:52

Mark Miller

3 Answers3