Doubt to use Replace together with Inner Join

Asked

Viewed 301 times

2

I’m using the Inner Join to combine two tables:

SELECT eventos.colab_id, colaboradores.setor 
FROM colaboradores INNER JOIN eventos
ON colaboradores.id = eventos.colab_id

So far so good, however I have cases where I need to eliminate the character " of eventos.colab_id. The REPLACE does this, but I am not knowing how to use the syntax along with the Inner Join.

mysql replace inner-join

  • I can not update because I just want to consult, without changing the table.

2 answers

2


Does not change the JOIN in the question, can apply replace normally:

SELECT REPLACE( eventos.colab_id, '"', '') AS colabid, colaboradores.setor 
FROM colaboradores INNER JOIN eventos
ON colaboradores.id = eventos.colab_id

If it’s in comparison:

SELECT eventos.colab_id, colaboradores.setor 
FROM colaboradores INNER JOIN eventos
ON colaboradores.id = REPLACE( eventos.colab_id, '"', '');

Now, if this is the second case it is much better to fix the bank and leave the field as numeric. To definitely remove the quotation marks:

UPDATE eventos SET colab_id = REPLACE( colab_id, '"', '');

and after that convert the column to whole, to avoid problems.

1

The function REPLACE of MySql has the following syntax:

REPLACE(str, find_string, replace_with)

Parameters:

  1. A string
  2. The string you want to replace
  3. The string that will replace the fetched occurrence of item 2

In your case, it would look like this:

    SELECT eventos.colab_id, REPLACE(eventos.colab_id,'"',''),  colaboradores.setor 
    FROM colaboradores INNER JOIN eventos
    ON colaboradores.id = eventos.colab_id

If it’s on the second table, it looks like this:

   SELECT eventos.colab_id,  colaboradores.setor 
   FROM colaboradores INNER JOIN eventos
   ON colaboradores.id = REPLACE(eventos.colab_id,'"','')

Reference: https://www.w3resource.com/mysql/string-functions/mysql-replace-function.php

Browser other questions tagged mysql replace inner-join

You are not signed in. Login or sign up in order to post.