1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

SQL query to select a variety of names

Discussion in 'XenForo Development Discussions' started by Fuhrmann, Nov 11, 2011.

  1. Fuhrmann

    Fuhrmann Well-Known Member

    I have a variable which contains this name: Roberto Teixeira Alves Freitas

    And in my Database I have this names:
    Code:
    John Carpenter
    Roberto Alves
    Julian Magnata
    Roberto Teixeira
    Roberto Freitas
    Teixeira Alves
    Joey Ramone
    Teixeira Roberto

    I need to do a SQL query which selects all this variety of the name. The result have to be this:

    Code:
    Roberto Alves
    Roberto Teixeira
    Freitas Roberto
    Teixeira Alves
    Teixeira Roberto
    Alves Freitas
    
    There is a way to do that?

    I already tried this:

    Code:
    SELECT Name FROM My_Table WHERE (Name LIKE '%Roberto%" OR Name LIKE '%Teixeira%") AND (Name LIKE '%Alves%" OR Name LIKE '%Freitas%")
    But this query will only give me this results:

    Code:
    Roberto Alves
    Teixeira Alves
    Teieira Roberto
    Freitas Roberto
    
    But I can get this name Alves Freitas and Roberto Teixeira. Any help?
     
  2. Brogan

    Brogan XenForo Moderator Staff Member

    Is there any reason why you're using OR AND OR?

    Can't you just use OR OR OR?
     
  3. Fuhrmann

    Fuhrmann Well-Known Member

    Yes, becuase there is a lot of others name like "Robert" something...in the database.

    And I just want the Roberto (or Teixeira, or Alves, or.......)

    Thank you.
     
  4. Brogan

    Brogan XenForo Moderator Staff Member

    So won't you just have to add all variables to each OR array?

    Either that or have 2 fields; first_name, last_name.
     
  5. Fuhrmann

    Fuhrmann Well-Known Member


    Thank you Brogan!

    This is what I have done, and it works:

    Name: Jose Vitor Teixeira de Freitas

    Code:
    SELECT Name FROM My_Table WHERE  OR (Name like '*jose*' AND Name like '*vitor*')  OR (Name like '*jose*' AND Name like '*teixeira*')  OR (Name like '*jose*' AND Name like '*freitas*')  OR (Name like '*vitor*' AND Name like '*teixeira*')  OR (Name like '*vitor*' AND Name like '*freitas*')  OR (Name like '*teixeira*' AND Name like '*freitas*')  ORDER BY Name ASC
    And it works.
     

Share This Page