Replace with variable - mysql

Asked

Viewed 115 times

0

How can I do Replace using a variable?

A certain variável receives values from a query, so I need to make a Replace in a given column, with the obtained values ,how can I do this? follows example in fiddle:

http://sqlfiddle.com/#! 9/6f8f1f/16/0

It turns out that the values obtained, appear as only value and the results are not expected

 SELECT
@enderecoHashImagem := concat(substr(contenthash,1,2),'/',substr(contenthash,3,2),'/',contenthash) as enderecoHashImagem,
@nomeImagem := filename as nomeImagem,
Replace(questiontext, concat('@@PLUGINFILE@@/',@nomeImagem), GROUP_CONCAT(@enderecoHashImagem)) AS questiontext

FROM mdl_question q 

inner join mdl_files f on f.itemid=q.id

where component = 'question'
mysql variables replace
  • 1

    This is happening because its variable @grupoEndereco is returning null.

  • @Robertodefields in the fiddle example this is @groupEnderco output: 8c/a7/8ca79795dc7c3c7fc836faac6d0d7422627486cb,8c/a7/8ca79795dc7c3c7fc836faac6d0d7422627486cb

  • two addresses separated by comma

  • 1

    Does so: SELECT
@enderecoHashImagem := concat(substr(contenthash,1,2),'/',substr(contenthash,3,2),'/',contenthash) as enderecoHashImagem,
@nomeImagem := filename as nomeImagem, @grupoEndereco := GROUP_CONCAT(@enderecoHashImagem) AS grupoEndereco,
@grupoEndereco, Replace(questiontext, concat('@@PLUGINFILE@@/',@nomeImagem), @grupoEndereco) AS questiontext

FROM mdl_question q 

inner join mdl_files f on f.itemid=q.id

where component = 'question' you will see that the second time you use this variable will return null.

  • @Robertodecampos vdd, the second returns null

  • Is it possible to do what I want, use replace with the values of this variable ?

  • @Robertodecampos now I’m not getting anymore null, but the result is not yet as expected, if you can take a look, thank you

  • To tell you the truth, I’ve never used variables this way, what I usually do is duplicate the code, I’ll put as an answer for you to understand better.

Show 3 more comments

2 answers

0

I’ve never worked with variables this way, which I usually do when I need to treat the information on SELECT more than once it is, duplicate the code, in your case it would look like this:

SELECT
  concat(
    substr(
      contenthash,
      1,
      2
    ),
    '/',
    substr(
      contenthash,
      3,
      2
    ),
    '/',
    contenthash
  ) as enderecoHashImagem,
  filename as nomeImagem,
  GROUP_CONCAT(
    concat(
      substr(
        contenthash,
        1,
        2
      ),
      '/',
      substr(
        contenthash,
        3,
        2
      ),
      '/',
      contenthash
    )
  ) AS grupoEndereco,
  Replace(
    questiontext,
    concat(
      '@@PLUGINFILE@@/',
      filename
    ),
    GROUP_CONCAT(
    concat(
      substr(
        contenthash,
        1,
        2
      ),
      '/',
      substr(
        contenthash,
        3,
        2
      ),
      '/',
      contenthash
    )
  )
  ) AS questiontext
  FROM mdl_question q
  inner join mdl_files f on f.itemid=q.id
  where component = 'question'

  • in this case the result remains the same, the values of the query are being treated as only value

0


I did the following, I make the consultation using group_concat of the values I wish to replace others' to get them concatenated separated by commas. I use the Replace and Substring_index for, at each substitution, the value that has already been used is excluded, to determine the next value to be obtained using Substring_index again.

It’s working, maybe it’s not the proper method, because if there is more than 20 values I wish to replace gives error, but up to 20 works.

Follows sqlfiddle:

http://sqlfiddle.com/#! 9/6f8f1f/16/0

-- borrowed from https://stackoverflow.com/q/7745609/808921

CREATE TABLE IF NOT EXISTS `mdl_question` (
  `id` int(6) unsigned NOT NULL,
  `questiontext` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `mdl_question` (`id`, `questiontext`) VALUES
  ('1', '<img src="@@PLUGINFILE@@/imagem1.svg"> <img src="@@PLUGINFILE@@/imagem2.svg"> <img src="@@PLUGINFILE@@/imagem3.svg">');

CREATE TABLE IF NOT EXISTS `mdl_files` (
  `id` int(6) unsigned NOT NULL,
  `contenthash` varchar(200) NOT NULL,
  `filename` varchar(200) NOT NULL,
  `component` varchar(200) NOT NULL,
  `itemid` varchar(200) NOT NULL,  

  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `mdl_files` (`id`, `contenthash`, `filename`, `component`, `itemid`) VALUES
  ('1','8ca79795dc7c3c7fc836faac6d0d7422627486cb','imagem1.svg','question', '1'),
  ('2','418679c9ecac4eb42452d4c51332710d24f6de8a','imagem2.svg','question', '1'),
  ('3','418679c9ecac4eb42452d4c51332710d24f6de8a','imagem3.svg','question', '1')

SELECT
Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(questiontext, 
concat('@@PLUGINFILE@@/',SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(filename),',',-20),',',1)), SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(concat(substr(contenthash,1,2),'/',substr(contenthash,3,2),'/',contenthash)),',',-20),',',1)),
concat('@@PLUGINFILE@@/',SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(filename),',',-19),',',1)), SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(concat(substr(contenthash,1,2),'/',substr(contenthash,3,2),'/',contenthash)),',',-19),',',1)),
concat('@@PLUGINFILE@@/',SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(filename),',',-18),',',1)), SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(concat(substr(contenthash,1,2),'/',substr(contenthash,3,2),'/',contenthash)),',',-18),',',1)),
concat('@@PLUGINFILE@@/',SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(filename),',',-17),',',1)), SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(concat(substr(contenthash,1,2),'/',substr(contenthash,3,2),'/',contenthash)),',',-17),',',1)),
concat('@@PLUGINFILE@@/',SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(filename),',',-16),',',1)), SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(concat(substr(contenthash,1,2),'/',substr(contenthash,3,2),'/',contenthash)),',',-16),',',1)),
concat('@@PLUGINFILE@@/',SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(filename),',',-15),',',1)), SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(concat(substr(contenthash,1,2),'/',substr(contenthash,3,2),'/',contenthash)),',',-15),',',1)),
concat('@@PLUGINFILE@@/',SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(filename),',',-14),',',1)), SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(concat(substr(contenthash,1,2),'/',substr(contenthash,3,2),'/',contenthash)),',',-14),',',1)),
concat('@@PLUGINFILE@@/',SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(filename),',',-13),',',1)), SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(concat(substr(contenthash,1,2),'/',substr(contenthash,3,2),'/',contenthash)),',',-13),',',1)),
concat('@@PLUGINFILE@@/',SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(filename),',',-12),',',1)), SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(concat(substr(contenthash,1,2),'/',substr(contenthash,3,2),'/',contenthash)),',',-12),',',1)),
concat('@@PLUGINFILE@@/',SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(filename),',',-11),',',1)), SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(concat(substr(contenthash,1,2),'/',substr(contenthash,3,2),'/',contenthash)),',',-11),',',1)),
concat('@@PLUGINFILE@@/',SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(filename),',',-10),',',1)), SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(concat(substr(contenthash,1,2),'/',substr(contenthash,3,2),'/',contenthash)),',',-10),',',1)),
concat('@@PLUGINFILE@@/',SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(filename),',',-9),',',1)), SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(concat(substr(contenthash,1,2),'/',substr(contenthash,3,2),'/',contenthash)),',',-9),',',1)),
concat('@@PLUGINFILE@@/',SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(filename),',',-8),',',1)), SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(concat(substr(contenthash,1,2),'/',substr(contenthash,3,2),'/',contenthash)),',',-8),',',1)),
concat('@@PLUGINFILE@@/',SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(filename),',',-7),',',1)), SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(concat(substr(contenthash,1,2),'/',substr(contenthash,3,2),'/',contenthash)),',',-7),',',1)),
concat('@@PLUGINFILE@@/',SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(filename),',',-6),',',1)), SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(concat(substr(contenthash,1,2),'/',substr(contenthash,3,2),'/',contenthash)),',',-6),',',1)),
concat('@@PLUGINFILE@@/',SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(filename),',',-5),',',1)), SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(concat(substr(contenthash,1,2),'/',substr(contenthash,3,2),'/',contenthash)),',',-5),',',1)),
concat('@@PLUGINFILE@@/',SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(filename),',',-4),',',1)), SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(concat(substr(contenthash,1,2),'/',substr(contenthash,3,2),'/',contenthash)),',',-4),',',1)),
concat('@@PLUGINFILE@@/',SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(filename),',',-3),',',1)), SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(concat(substr(contenthash,1,2),'/',substr(contenthash,3,2),'/',contenthash)),',',-3),',',1)),
concat('@@PLUGINFILE@@/',SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(filename),',',-2),',',1)), SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(concat(substr(contenthash,1,2),'/',substr(contenthash,3,2),'/',contenthash)),',',-2),',',1)),
concat('@@PLUGINFILE@@/',SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(filename),',',-1),',',1)), SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(concat(substr(contenthash,1,2),'/',substr(contenthash,3,2),'/',contenthash)),',',-1),',',1)) 

AS questiontext

FROM mdl_question q 

inner join mdl_files f on f.itemid=q.id

  • I know you must have a professional way of doing it but I don’t know

Browser other questions tagged mysql variables replace

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