1 votes

Is there an alternative to CONCAT() in mysql?

I need an alternative of the function CONCAT in SQL. The reason is that I am being asked in a school work to use in the following search for example an alternative to the function already mentioned:

SELECT CONCAT(A.NOMBRE, A.A_PATERNO, A.A_MATERNO) NOMBRE, M.NOMBRE 
FROM ALUMNOS A, MATERIAS M, CALIFICACIONES C 
WHERE A.NUM_CONTROL=C.NUM_CONTROL AND M.CVE_MATERIA=C.CVE_MATERIA;

0 votes

2 votes

What do you want to obtain? why an alternative? what limitations does the function have? CONCAT please edit and explain

0 votes

Yes, use a + to concatenate fields if they are varchar

1voto

Alvaro Montoro Points 38554

In MySQL you can concatenate in several ways:

  • Using CONCAT() which returns the string resulting from the union/concatenation of the parameters. This is the most common method, but it is the one you want to avoid. Example:

    SELECT CONCAT(nombre, ' ', apellido1, ' ', apellido2) AS nombre FROM persona;
  • Using CONCAT_WS() which returns the string resulting from the union of the parameters using the first one as a connector between the others (it is a variation of CONCAT() but with separator, so the WS : With Separator ). Example:

    SELECT CONCAT_WS(' ', nombre, apellido1, apellido2) AS nombre FROM persona;
  • Using pipes ( || ) you can tell MySQL to use the pipes for concatenation using the PIPES_AS_CONCAT (the following would also apply ANSI or the modes DB2 , MAXDB , MSSQL , ORACLE o POSTGRESQL although these last 5 are obsolete and should not be used ). Example:

    SET sql_mode=PIPES_AS_CONCAT;
    SELECT nombre || ' ' || apellido1 || ' ' || apellido2 AS nombre FROM persona;

The three options above will return the same result. I have created a example in DB Fiddle where you can see all three methods at work.

0 votes

Thank you very much friend very complete your answer, even that I could not use the variant that would have worked better that was with pipes lines because apparently it is not compatible with my version of MySQL which is 8.0.16, on this page techonthenet.com/oracle/functions/concat2.php whoever likes can see if his version is compatible, but thank you very much for the help.

0voto

Raul Zarate Points 71

You can use + to concatenate:

SELECT 
(A.NOMBRE+' '+A.A_PATERNO+' '+A.A_MATERNO) AS NOMBRE,
M.NOMBRE 
FROM ALUMNOS AS A 
INNER JOIN CALIFICACIONES C ON (C.NUM_CONTROL = A.NUM_CONTROL)
INNER JOIN MATERIAS AS M ON (M.CVE_MATERIA = C.CVE_MATERIA)

2 votes

Your answer is just code. Please see How to Answer . You are asked to explain what this code does (even though it may seem obvious to you, another user may not see it as simple).

0 votes

That concatenation returns 0, since "+" is for arithmetic operations, but thanks.

0 votes

I think in the publication you put SQL, but are you in Mysql? In which engine do you require it? if the data type is not a string type, convert it.

HolaDevs.com

HolaDevs is an online community of programmers and software lovers.
You can check other people responses or create a new question if you don't find a solution

Powered by:

X