Replace with variable - mysql

Question

Replace with variable - mysql

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

How can I make Replace using a variable?

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

link

It occurs that the values obtained, appear as single value and the results are not as 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 replace

asked by anonymous 27.07.2018 / 03:17

2 answers

0

I've never worked with variables like this, what I usually do when I need to treat the information in SELECT more than once 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'

27.07.2018 / 14:06

Select ABA - SAP SCRIPT Find variable value

score 0 · Accepted Answer

I did the following, I am querying using group_concat of the values I want to replace others, to get them concatenated separated by commas. I use Replace and Substring_index to, at each substitution, the value that has already been used is deleted, to determine the next value to be used Substring_index use again.

It is working, it may not be the right method, because if there is more than 20 values that I want to substitute gives error, but up to 20 works.

Follow sqlfiddle:

link

-- 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