php - ¿Cómo reemplazar cada otra instancia de un personaje en particular en una cadena MySQL?

string replace

Question

Cómo reemplazar el valor en la columna mysql por consulta, la columna es options y es de tipo varchar(255)

Desde

id   options
1    A|10|B|20|C|30
2    A|Positive|B|Negative

A

id   options
1    A|10,B|20,C|30
2    A|Positive,B|Negative

Lo estoy haciendo por php así.

<?php
    $str =  "A|10|B|20|C|30";
    $arr = explode("|",$str);
    $newArr = array();
    for($i=0;$i<count($arr);$i+=2){
      if($arr[$i] && $arr[$i+1]){
        $newArr[] = $arr[$i]."|".$arr[$i+1];
      }
    }
    echo "Before:".$str."/n";
    echo "After :".implode(",",$newArr);
?>

https://eval.in/841007

Así que en lugar de PHP, quiero hacer esto en MySQL.

Answer 1

Debe considerar almacenar sus datos en un esquema normalizado. En tu caso la tabla debería verse como:

| id | k |        v |
|----|---|----------|
|  1 | A |       10 |
|  1 | B |       20 |
|  1 | C |       30 |
|  2 | A | Positive |
|  2 | B | Negative |

Este esquema es más flexible y verás por qué.

Entonces, ¿cómo convertir los datos dados en el nuevo esquema? Necesitará una tabla auxiliar que contenga números de secuencia. Como su columna es varchar(255) , solo puede almacenar 128 valores (+ 127 delimitadores) en ella. Pero vamos a crear 1000 números. Puedes usar cualquier tabla con suficientes filas. Pero como cualquier servidor MySQL tiene la tabla information_schema.columns , la usaré.

drop table if exists helper_sequence;
create table helper_sequence (i int auto_increment primary key)
    select null as i
    from information_schema.columns c1
    join information_schema.columns c2
    limit 1000;

Usaremos estos números como posición de los valores en su cadena uniendo las dos tablas.

Para extraer un valor de una cadena delimitada puede usar la función substring_index() . El valor en la posición i será

substring_index(substring_index(t.options, ''|'', i  ), ''|'', -1)

En tu cadena tienes una secuencia de teclas seguida de sus valores. La posición de una tecla es un número impar. Entonces, si la posición de la clave es i , la posición del valor correspondiente será i+1

Para obtener el número de delimitadores en la cadena y limitar nuestra unión podemos usar

char_length(t.options) - char_length(replace(t.options, ''|'', ''''))

La consulta para almacenar los datos en una forma normalizada sería:

create table normalized_table
    select t.id
        , substring_index(substring_index(t.options, ''|'', i  ), ''|'', -1) as k
        , substring_index(substring_index(t.options, ''|'', i+1), ''|'', -1) as v
    from old_table t
    join helper_sequence s
      on s.i <= char_length(t.options) - char_length(replace(t.options, ''|'', ''''))
    where s.i % 2 = 1

Ahora ejecuta select * from normalized_table y obtendrás esto:

| id | k |        v |
|----|---|----------|
|  1 | A |       10 |
|  1 | B |       20 |
|  1 | C |       30 |
|  2 | A | Positive |
|  2 | B | Negative |

Entonces, ¿por qué este formato es una mejor opción? Además de muchas otras razones, una es que puede convertirlo fácilmente a su esquema anterior con

select id, group_concat(concat(k, ''|'', v) order by k separator ''|'') as options
from normalized_table
group by id;

| id |               options |
|----|-----------------------|
|  1 |        A|10|B|20|C|30 |
|  2 | A|Positive|B|Negative |

o al formato deseado

select id, group_concat(concat(k, ''|'', v) order by k separator '','') as options
from normalized_table
group by id;

| id |               options |
|----|-----------------------|
|  1 |        A|10,B|20,C|30 |
|  2 | A|Positive,B|Negative |

Si no le importa la normalización y solo quiere que se realice esta tarea, puede actualizar su tabla con

update old_table o
join (
    select id, group_concat(concat(k, ''|'', v) order by k separator '','') as options
    from normalized_table
    group by id
) n using (id)
set o.options = n.options;

Y soltar la normalized_table .

Pero entonces no podrás usar consultas simples como

select *
from normalized_table
where k = ''A''

Ver demo en rextester.com

Answer 2

Hum, creo que estás tratando de hacer algo como esto

SELECT GROUP_CONCAT(CONCAT(options,",") SEPARATOR "|") FROM Table.name;

Explico brevemente, tomo para cada fila el resultado y concatené "," y concatené toda la fila con el separador "|". Tendrá que cambiar Table.name con el nombre de su tabla

Si desea concatenar un valor más como A, B, C (no explicó de dónde proviene el valor ABC, digamos que ValueWhereABCisComingFrom):

SELECT GROUP_CONCAT(CONCAT(ValueWhereABCisComingFrom,"|",options) SEPARATOR ",") FROM Table.name;

Si mi mesa es así:

id | ValueWhereABCisComingFrom | options
0  | A    | 10
1  | B    | 20
2  | C    | 30

Tendrás algo así:

A|10,B|20,C|30

EDITAR 1

No hay manera de hacer eso en ese caso. No hay ninguna función como preg_replace en mysql. Todo lo que puedes hacer es reemplazar todos los "|" me gusta

SELECT  Replace(options, ''|'', '','') AS P
FROM `docs`;

En MariaDB, existe una función de este tipo, por lo que podría intentar pasar de una base a otra. Pero solo con MYSQL, de ninguna manera: /

Answer 3

No utilizando procedimientos almacenados, lo haría en 2 pasos:

1. Inserte la coma en la segunda aparición del carácter de canalización:

   update options set options = insert(options, locate(''|'', options, locate(''|'', options) + 1), 1, '','');

2. Inserte las comas restantes - ejecute la consulta N veces:

   update options set options = insert(options, locate(''|'', options, locate(''|'', options, length(options) - locate('','', reverse(options)) + 1) + 1), 1, '','');

   donde N =

   select max(round(((length(options) - length(replace(options, ''|'', ''''))) - 1 ) / 2) - 1) from options;

   (o no se moleste en contar y continúe ejecutando la consulta siempre y cuando no le diga "0 filas afectadas")

Comprobado con este conjunto de datos:

id   options
1    A|10|B|20|C|30
2    A|Positive|B|Negative
3    A|10|B|20|C|30|D|40|E|50|F|60
4    A|Positive|B|Negative|C|Neutral|D|Dunno

resultados en:

id   options
1    A|10,B|20,C|30
2    A|Positive,B|Negative
3    A|10,B|20,C|30,D|40,E|50,F|60
4    A|Positive,B|Negative,C|Neutral,D|Dunno

(Voy a proporcionar una explicación más adelante)

Answer 4

Puedes hacerlo creando una función.

CREATE FUNCTION doiterate(str TEXT, i INT, next INT, isp TINYINT(1))
  RETURNS TEXT
  BEGIN
    myloop: LOOP
      IF next = 0 THEN
        LEAVE myloop;
      END IF;
      IF isp = TRUE THEN
        set str = insert(str, i, 1, '','');
        set isp = FALSE;
        set i = next;
        set next = locate(''|'', str, i + 1);
        ITERATE myloop;
      ELSE
        set isp = TRUE;
        set i = next;
        set next = locate(''|'', str, i + 1);
        ITERATE myloop;
      END IF;
      LEAVE myloop;
    END LOOP;
    return str;
  END;

y llamándolo así:

SELECT t.`column`,
  @loc := locate(''|'', t.`column`) as position,
  @next := locate(''|'', t.`column`, @loc +1) as next,
  @isp := 0 is_pipe,
  @r := doiterate(t.column, @loc, @next, @isp) as returnstring
from test t;

Supongo que serás lo suficientemente inteligente como para

• cambiar el nombre de la tabla y la columna
• inserta esto en una solicitud de actualización

Puede cambiar @isp: = a 1 si obtuve el cambio de coma / coma incorrecto (asumí que la segunda tubería debería cambiarse a coma)

Answer 5

Manifestación

Demo rextester

Explicación

Esto podría resolverse con relativa facilidad si solo MySQL tuviera una función de reemplazo de expresiones regulares, pero desafortunadamente no . Así que escribí una - ver esta entrada de blog . La "versión avanzada" es necesaria aquí para permitirle realizar una sustitución recursiva en la coincidencia encontrada para el reemplazo. Entonces se puede usar el siguiente SQL relativamente simple:

SQL (código de función omitido por brevedad)

SELECT id,
       options AS `before`,
       reg_replace(options,
                   ''//|.*//|'', -- 2 pipe symbols with any text in between
                   ''//|$'',     -- Replace the second pipe symbol
                   '','',        -- Replace with a comma
                   FALSE,      -- Non-greedy matching
                   2,          -- Min match length = 2 (2 pipe symbols)
                   0,          -- No max match length
                   0,          -- Min sub-match length = 1 (1 pipe symbol)
                   0           -- Max sub-match length = 1 (1 pipe symbol)
                   ) AS `after`
FROM tbl;