learning learning - 4 months ago 18
SQL Question

Find the all customer names consisting of three or more words (for example King George V)

schema:




customers(name, mailid, city)


What to find:



Find all customer names consisting of three or more words (for example
King George V
).

What I tried:



select name from customers
where name like
'%[A-Za-z0-9][A-Za-z0-9]% %[A-Za-z0-9][A-Za-z0-9]% %[A-Za-z0-9][A-Za-z0-9]%'


what is surprising me:



If I am trying for two words (removing the last
%[A-Za-z0-9]%
from my query), its working fine but its not working for three words :(

Answer

MySQL Solution:

If a name has words separated by space character, then,

Try the following:

select name from customers 
where ( length( name )
        -
        length( replace( name, ' ', '' ) ) + 1
      ) >= 3
Comments