CODEWITHSUNDEEP
postgresqlcount
Shisa
Shisa

Reputation: 580

Count all rows containing [Word] in [Column] in postgresql

I need to count and also get output of all rows in a table containing a given word in a specific column. Something like

ID    Name    Fave    
678   Adam    cannot bear this
355   Baloo   bear is a bear 
245   Cheeta  runs fast

So that I can get an output of '2' (and not '3') on counting the number of rows containing the word 'bear' in the column 'Fave', and an output of the first two rows for the tabular output/select rows.

I've tried 

SELECT * WHERE regexp_matches(Fave, 'bear') FROM table_name

but I'm getting a syntax error near FROM so I'm WHERE is where the trouble is at. Any pointers/help, please?

Upvotes: 1

Views: 516

Answers (3)

Abelisto
Abelisto

Reputation: 15624

select * from table_name where Fave ~* '\mbear\M';

~* - case-insensitive regexp matches
'\m...\M' - single word, so 'taddy bear' is matching and taddybear is not.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

The FROM goes before the WHERE:

SELECT *
FROM table_name
WHERE regexp_matches(Fave, 'bear') ;

You can also use LIKE, of course, but the issue is the order of the clauses in the query.

Upvotes: 1

mccalljt
mccalljt

Reputation: 796

Are you looking for:

SELECT * FROM table_name WHERE Fave like '%bear%'

Upvotes: 1

Related Questions