String Concatenation in SQL

7

I make a query in a table in my MySQL database that returns the following fields: 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, everything is calm, it returns me waiting for you. As below:

Butinadditiontobeingabletoqueryfieldtofieldandcomparecontent.Iwantedittobepossibletoperformaquerybyfullnameoronlypartofthefullname.Example:"Luitame de Oliveira" and the query returns some record, because following the query above it only returns me some value if I search for nome or sobrenome . But, if I do the join of nome + sobrenome the query does not return anything to me.

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?

    
asked by anonymous 15.01.2014 / 16:13

4 answers

9

You are looking for full-text search capabilities (which, since MySQL 5.6.4 also work with InnoDB ).

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

And for the query:

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

If the MySQL features are not enough (usually they are not) you can use an external tool like Solr , a href="http://sphinxsearch.com/"> Sphinx or Elasticsearch . These tools have facilities that leave Boolean queries and MySQL natural language in flip-flops, being able to treat abbreviations, synonyms, spelling errors, similar words , etc. In addition, 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 did not want to get into this level of detail in the answer, but as @ Kenny made a query without using Boolean Mode it is important to note that even without special operators, this option makes a difference if you use MyISAM (and I have retained my habits at that time). With Boolean Mode spaces act as the OU operator (and you can build queries similar 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 50% exclusion rule (it should not be relevant in your case unless more than 50% of your users are called Mary or John, but it is good to know if you index some very popular word).

    
15.01.2014 / 17:34
7

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 you need better performance, you can use Fulltext , as follows:

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

The query would look like this:

    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")
    
15.01.2014 / 16:41
1

As already mentioned, you can use Full-Text Index . In StackOverflow in English, I explained how to use this index in MySQL. link

    
31.01.2014 / 18:34
0

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

In addition, since the full name is separated into two columns, it is necessary to remember that there is space between "Luitame" and "Oliveira".

I can not 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'
    
15.01.2014 / 16:42