String concatenation in SQL

Question

I perform a query in a table in my Mysql database that returns the following fields to me: usuarioId nome sobrenome idade sexo escolaridade modeloNome hora

Follow the sql below:

SELECT 
    sv_users.userID as `usuarioId`,
    sv_users.userNome as `nome`,
    sv_users.userSobrenome as `sobrenome`, 
    sv_users.userDataNascimento as `idade`, 
    sv_users.userSexo as `sexo`, 
    sv_users.userEscolaridade as `escolaridade`, 
    sv_modelos.modelNome as `modeloNome`,
    sv_jogo.`Data` as `hora`    
FROM sv_jogo      
   INNER JOIN sv_users ON sv_jogo.UsuarioId = sv_users.userID
   INNER JOIN sv_modelos ON sv_users.userModelId = sv_modelos.modelId
WHERE 
   (sv_users.userNome LIKE 'Luitame%' OR sv_users.userSobrenome LIKE 'Luitame%') 
OR 
   (sv_users.userNome LIKE '%Luitame' OR sv_users.userSobrenome LIKE '%Luitame') 
OR 
   (sv_users.userNome = 'Luitame' OR sv_users.userSobrenome = 'Luitame')

So far all quiet, returns me waiting. As below:

It’s just that you can browse the field and compare the contents. I wanted it to be possible to perform a full name query or only part of the full name. Example: "Luitame de Oliveira" and the query return me some record, because following the above query it only returns me some value if I search for the nome or sobrenome. But if I make the junction of nome + sobrenome the query does not return me anything.

As in the example below:

SELECT 
    sv_users.userID as `usuarioId`,
    sv_users.userNome as `nome`,
    sv_users.userSobrenome as `sobrenome`, 
    sv_users.userDataNascimento as `idade`, 
    sv_users.userSexo as `sexo`, 
    sv_users.userEscolaridade as `escolaridade`, 
    sv_modelos.modelNome as `modeloNome`,
    sv_jogo.`Data` as `hora` 
FROM sv_jogo 
    INNER JOIN sv_users ON sv_jogo.UsuarioId = sv_users.userID
    INNER JOIN sv_modelos ON sv_users.userModelId = sv_modelos.modelId
WHERE 
    (sv_users.userNome LIKE 'Luitame de Oliveira%' OR sv_users.userSobrenome LIKE 'Luitame de Oliveira%') 
OR 
    (sv_users.userNome LIKE '%Luitame de Oliveira' OR sv_users.userSobrenome LIKE '%Luitame de Oliveira') 
OR 
    (sv_users.userNome = 'Luitame de Oliveira' OR sv_users.userSobrenome = 'Luitame de Oliveira')

Check out the result:

Any suggestions?

Answer 1

You are searching for capabilities of full-text search (that since Mysql 5.6.4 also work with Innodb).

ALTER TABLE sv_users ADD FULLTEXT INDEX (userNome, userSobrenome); 

And for the consultation:

SELECT * 
FROM sv_users
WHERE MATCH (userNome, userSobrenome) 
AGAINST ('Luitame de Oliveira' IN BOOLEAN MODE)

If Mysql facilities are not sufficient (usually not) you can use an external tool like Weld, Sphinx or Elasticsearch. These tools have facilities that leave the boolean queries and in natural language Mysql in flip flops, can handle abbreviations, synonyms, spelling errors, similar words, etc. Also, they are much more robust when the number of queries grows.

---

UPDATE:

Examples demonstrating my point about the Mysql version and Full Text Search support in Innodb.

Demo I: SQL Fiddle - Mysql 5.5 (Myisam)
Demo II: SQL Fiddle - Mysql 5.6 (Myisam and Innodb)

I didn’t want to get into that level of cuteness in the answer, but as @Kenny made an appointment without using the Boolean Mode it is important to note that even without special operators, this option makes a difference if you use the engine Myisam (and I kept my habits from that time). With the Boolean Mode spaces act like the operator OU (and you can build similar queries to Google with +, -, etc.) In the case of Innodb the two queries return the same result if you do not use a special operator, but it is still interesting to be used to avoid the the 50 % exclusion rule% (should not be relevant in your case unless more than 50% of your users are called Maria or João, but it is good to know if you index some very popular word).

Answer 2

Try this:

    SELECT 
    sv_users.userID as `usuarioId`,
    sv_users.userNome as `nome`,
    sv_users.userSobrenome as `sobrenome`, 
    CONCAT_WS(` `, `nome`, `sobrenome`) as nome_completo,
    sv_users.userDataNascimento as `idade`, 
    sv_users.userSexo as `sexo`, 
    sv_users.userEscolaridade as `escolaridade`, 
    sv_modelos.modelNome as `modeloNome`,
    sv_jogo.`Data` as `hora` 
FROM sv_jogo 
    INNER JOIN sv_users ON sv_jogo.UsuarioId = sv_users.userID
    INNER JOIN sv_modelos ON sv_users.userModelId = sv_modelos.modelId
WHERE 
    (nome_completo LIKE 'Luitame de Oliveira%') 
OR 
    (nome_completo LIKE '%Luitame de Oliveira') 
OR 
    (nome_completo = 'Luitame de Oliveira')

If the volume of data is too large and better performance is required, you can use the Fulltext, as follows:

ALTER TABLE sv_users ADD FULLTEXT INDEX (userNome, userSobrenome); 

The consultation would be as follows:

    SELECT 
    sv_users.userID as `usuarioId`,
    sv_users.userNome as `nome`,
    sv_users.userSobrenome as `sobrenome`, 
    sv_users.userDataNascimento as `idade`, 
    sv_users.userSexo as `sexo`, 
    sv_users.userEscolaridade as `escolaridade`, 
    sv_modelos.modelNome as `modeloNome`,
    sv_jogo.`Data` as `hora` 
FROM sv_jogo 
    INNER JOIN sv_users ON sv_jogo.UsuarioId = sv_users.userID
    INNER JOIN sv_modelos ON sv_users.userModelId = sv_modelos.modelId
WHERE 
    MATCH(sv_users.userNome, sv_users.userSobrenome) AGAINST("Luitame de Oliveira")

Answer 3

Like I said, you can use a Full-Text Index. In Stackoverflow, I explained how to use this index in Mysql. https://stackoverflow.com/questions/18492876/sql-specific-statement-query/18492997#18492997

Answer 4

The problem seems to me to be due to the fact that the first name ("Luitame") is in one column ("userNome") and the rest of the name ("de Oliveira") in another column (userSobrenome). Therefore, searching for the full name in one of the columns will not even produce results.

Also, as the full name is separated into two columns, it is necessary to remember that there is no the space between "Luitame" and "de Oliveira".

I have no way to test here right now, but maybe this test will work:

SELECT 
    sv_users.userID as `usuarioId`,
    sv_users.userNome as `nome`,
    sv_users.userSobrenome as `sobrenome`, 
    sv_users.userDataNascimento as `idade`, 
    sv_users.userSexo as `sexo`, 
    sv_users.userEscolaridade as `escolaridade`, 
    sv_modelos.modelNome as `modeloNome`,
    sv_jogo.`Data` as `hora` 
FROM sv_jogo 
    INNER JOIN sv_users ON sv_jogo.UsuarioId = sv_users.userID
    INNER JOIN sv_modelos ON sv_users.userModelId = sv_modelos.modelId
WHERE 
    concat(concat(sv_users.userNome, ' '),sv_users.userSobrenome), LIKE '%Luitame de Oliveira'