I'm unaware of any built-in functions in Vertica which allow us to directly calculate the number of occurrences of a string within another string, but there is a simple work around using the LENGTH function.
Say I have a table named TEST with the following data:
Code: Select all
dbadmin=> SELECT c1 FROM test;
c1
-------------------------------------------------------------------------------------------
If I wasn't married to Jane, I'd marry Vertica.
Vertica 6 is better than Vertica 5 which is better than Vertica 4 which is better than...
Vertica tastes like chicken.
Learning how to use Vertica is fun because Vertica is cool.
Vertica Vertica Vertica Vertica I love Vertica
(5 rows)
Code: Select all
dbadmin=> SELECT c1 the_text,
dbadmin-> (length(c1) - length(REPLACE(c1, 'Vertica', ''))) / length('Vertica') vertica_appearances
dbadmin-> FROM test;
the_text | vertica_appearances
-------------------------------------------------------------------------------------------+---------------------
If I wasn't married to Jane, I'd marry Vertica. | 1
Vertica 6 is better than Vertica 5 which is better than Vertica 4 which is better than... | 3
Vertica tastes like chicken. | 1
Learning how to use Vertica is fun because Vertica is cool. | 2
Vertica Vertica Vertica Vertica I love Vertica | 5
(5 rows)
Here’s another method:
Code: Select all
dbadmin=> SELECT c1 the_text,
dbadmin-> SUM(SIGN(instr(c1, 'Vertica', 1, rn))) vertica_appearances
dbadmin-> FROM (SELECT c1,
dbadmin(> row_number() over (partition by c1) rn
dbadmin(> FROM test
dbadmin(> CROSS join tables) foo
dbadmin-> GROUP BY c1;
the_text | vertica_appearances
-------------------------------------------------------------------------------------------+---------------------
If I wasn't married to Jane, I'd marry Vertica. | 1
Learning how to use Vertica is fun because Vertica is cool. | 2
Vertica Vertica Vertica Vertica I love Vertica | 5
Vertica 6 is better than Vertica 5 which is better than Vertica 4 which is better than... | 3
Vertica tastes like chicken. | 1
(5 rows)