CODEWITHSUNDEEP
sqlpostgresql
user2983258
user2983258

Reputation: 161

Select only when the field has more than one word

SELECT name FROM clients;

Table clients

id | name | 
1    John  
2    John Bravo
3    John Alves
4    Jo

In postgres, how can I select only names with more than one word? For example. This should be the output:

John Bravo
John ALves

Upvotes: 0

Views: 7884

Answers (5)

Soumodipta Jana
Soumodipta Jana

Reputation: 1

SELECT *
FROM your_table
WHERE LENGTH(name) - LENGTH(REPLACE(name, ' ', '')) >= 1;

Upvotes: 0

Erlock
Erlock

Reputation: 1968

LIKE is an option, or you can use split_part:

SELECT name FROM clients WHERE split_part(trim(name), ' ', 2) <> ''

Upvotes: 0

Kizito Ikapel
Kizito Ikapel

Reputation: 69

First you may have to find the number of words in the column, then only select where the number of words is greater than 1.

For example:

SELECT LENGTH(name) - LENGTH(REPLACE(name , ' ', ''))+1 FROM clients;

This will return the number of words in each row, which we have in the field name.

Then you can proceed putting this in a nested select SQL statement something like : 

SELECT LENGTH(name) - LENGTH(REPLACE(name, ' ', ''))+1 as mycheck, name FROM clients where (SELECT LENGTH(name) - LENGTH(REPLACE(name, ' ', ''))+1)>1

This will return only where words are greater than 1 (>1). Else you can user >2 to return columns with more than 2 words.

Upvotes: 0

Gabriel Petrovay
Gabriel Petrovay

Reputation: 21924

If word means to you a space delimited token, then the following would do the trick:

SELECT name FROM clients WHERE name LIKE '% %';

But this will also give you those that have empty names made out of spaces only. Also performance-wise, this will be a costly query.

Upvotes: 1

PeterRing
PeterRing

Reputation: 1797

I think just test if the name contains a space: where name like '% %' But this will give you some problem if you name can contain space char befor or after the name, like: ' JOHN' or 'Luck '

Upvotes: 2

Related Questions