CODEWITHSUNDEEP
mysqlsql
Jordy
Jordy

Reputation: 1049

Query to check if a certain row has 2 words

How to return rows where a column has 2 words (that is, strings separated by a space) in it?

It must be purely using SQL.

SELECT * FROM table WHERE name (has 2 strings in it);

Upvotes: 5

Views: 3569

Answers (7)

Mike Wiley
Mike Wiley

Reputation: 1

SELECT * FROM table_name WHERE name LIKE '% %'

Upvotes: 0

artico
artico

Reputation: 376

Use % between words. Example:

SELECT * FROM Table WHERE Col LIKE '%word1%word2%'

Upvotes: 0

Tushar
Tushar

Reputation: 3623

This perfectly works for me

You can use 'AND' condition and Like Operator with wildcards (%).

SELECT * FROM table_name WHERE name LIKE '%Word1%' AND name LIKE '%Word2%'

Upvotes: 4

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726839

I dont know the names when querying. Its a big dataset. I only have to check if the name contains a spacebar basically (from a comment).

If you want to distinguish names that have two parts from one-part and three-plus-part names, you can use regular expression:

SELECT * FROM my_table WHERE name REGEXP '^[^ ]+[ ]+[^ ]+$'

This regular expression matches when the entire string consists of two non-empty parts containing no spaces, with one or more space separating them.

Upvotes: 6

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44864

You can use the following technique

 mysql> select length('first name')-length(replace('first name',' ','')) as diff;
    +------+
    | diff |
    +------+
    |    1 |
    +------+

i.e. get the difference of actual name and the name after replacing space, and if its 1 then you have the value as firstname lastname

So the query may look like

select * from table
where
length(col_name)-length(replace(col_name,' ','')) = 1

Upvotes: 0

jarlh
jarlh

Reputation: 44796

SELECT * FROM table WHERE concat(' ',name,' ') like '% str1 %'
                      AND concat(' ',name,' ') like '% str2 %'

The extra blanks are there to separate words.

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

How about simply:

...
WHERE [name] LIKE '%Word1%'
AND [name] LIKE '%Word2%'

Upvotes: 0

Related Questions