Update text pattern in the entire database

Question

Update text pattern in the entire database

Navigation

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

0

I have the following scenario: I have mysql server and defined multiple database and tables. the some fields of these tables I have text that includes the name of documents Example 1:

DocRel
CCTR0000007

example 2:

Memo
el documento CCTR0000007 fue cancelado

I am trying to do the following: There is some way to search and update in all places the text with the following search terms:

tamaño de string definido: ejemplo 'CCTR0000007' 11 caracteres
pattern de string definido: ejemplo 'CCTR0000007' todos los que comiencen con CCTR

and update the document by increasing the zeroes '0' example:

CCTR0000007
a:
CCTR00000000000007

why: why should I screw up thinking that the documentation should not be very broad and apparently already go through document CCTR0920008 and in a couple of months they will reach the numerical limit.

and the examples these like that do not work me very well:

UPDATE t1 SET NUMER=CONCAT('00000',NUMER) WHERE LENGTH(NUMER)=1;   
UPDATE t1 SET NUMER=CONCAT('0000',NUMER) WHERE LENGTH(NUMER)=2;  
UPDATE t1 SET NUMER=CONCAT('000',NUMER) WHERE LENGTH(NUMER)=3;  

SELECT LPAD(NUMER,6,'0') AS NUMER
FROM ...

mysql

asked by Francisco Núñez 04.01.2018 в 23:41

source

2 answers

Change order of elements in XML document node conversion to a data type?

score 0 · Answer 1

Hello! Is it necessary to do it with MySQL? You could do it with a PHP script:

//Poner datos de BD en un array.-
//...

//Longitud que debe tener en números, sin contar el CCTR.-
$longitud = 11;

//Recorrer y reemplazar.-
$arrayUpdate = [];
foreach($array as $input){

  $arrayUpdate['id'] = $input['id'];

  //Buscamos todas las apariciones de CCTR en la primera celda de la tabla.-
  $arrayUpdate['nombre'] = "";
  $apariciones = explode('CCTR', $input['nombre']);
  foreach(explode('CCTR', $input['nombre']) as $parte){
    //Agregamos '0' de acuerdo con $longitud.-
    $ceros = str_repeat('0', $longitud - strlen(explode(' ', $parte)[0]));
    $arrayUpdate['nombre'] .= 'CCTR'.str_repeat("0", $ceros).$parte;
  }
  $arrayUpdate['nombre'] = $apariciones[0].$arrayUpdate['nombre'];

  //Buscamos todas las apariciones de CCTR en la segunda celda de la tabla.-
  $arrayUpdate['descripcion'] = "";
  $apariciones = explode('CCTR', $input['descripcion']);
  foreach(explode('CCTR', $input['descripcion']) as $parte){
    //Agregamos '0' de acuerdo con $longitud.-
    $ceros = str_repeat('0', $longitud - strlen(explode(' ', $parte)[0]));
    $arrayUpdate['descripcion'] .= 'CCTR'.str_repeat("0", $ceros).$parte;
  }
  $arrayUpdate['descripcion'] = $apariciones[0].$arrayUpdate['descripcion'];

  //Ejecutamos query de Update.-
}

It is not the most elegant way, but if time is pressing you I think it may be a good option. I could not test the code since I do not have your bd, but it should be ok.

I would recommend backing up the database before doing this. Also check that foreign keys are not lost.

I hope it serves you, Greetings and success!

score 0 · Answer 2

It's easy to use LPAD from MySQL . Look on this web page LPAD

In your code it would be something like this:

UPDATE tutabla set DocRel = LPAD(trim(LEFT(DocRel,11)),18, '0') 
WHERE DocRel like 'CCTR%';

Read the information that I left in the link because possibly to execute this instruction you have to configure before this:

SET SQL_SAFE_UPDATES=0;

After you update it, you return it to the default value.

Observation: Before trying this please make a backup in case something does not go well do not affect the data.

That's it. A greeting.