CODEWITHSUNDEEP
sqlstringpostgresqlwhere-clause
PRYDAX
PRYDAX

Reputation: 25

SQL get fields in a column where a word appears only once

So say I have this table:

  -- column --
  word1
  word2
  word3 word4
  word

How do I get only the fields from this colum where 'word' appears only once? Like this:

  -- colum --
  word1
  word2
  word

I am using PostgreSQL.

Thanks in advance!

Upvotes: 1

Views: 58

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

I interpret the question as your wanting values that only consist of one word. This is another way of saying that there is no space:

select *
from t
where col not like '% %';

If you happen to have empty strings (but not NULL values) on rows and you don't want those, you can ensure that there is a value using:

select *
from t
where col not like '% %' and col like '%_%'

That does not seem necessary based on your sample data.

Upvotes: 0

Popeye
Popeye

Reputation: 35920

You can use the char_length of the column and use it as follows:

select * from your_table
where char_length(replace(col, 'word', '')) 
       = char_length(col) - char_length('word')

Upvotes: 1

GMB
GMB

Reputation: 222582

One option uses string functions like so:

select *
from mytable
where char_length(col) - char_length(replace(col, 'word', '')) = char_length('word')

The idea is to replace all occurrences of "word" in the string, and take the difference of the resulting string with the original. If the difference is 4, then we know there was only one match.

Upvotes: 1

Related Questions