¿Cómo extraer la palabra enésima y contar las ocurrencias de palabras en una cadena MySQL?

regex word-count

Question

Me gustaría tener una consulta mysql como esta:

select <second word in text> word, count(*) from table group by word;

Todos los ejemplos de expresiones regulares en mysql se utilizan para consultar si el texto coincide con la expresión, pero no para extraer texto de una expresión. ¿Hay tal sintaxis?

Answer 1

Como han dicho otros, mysql no proporciona herramientas regex para extraer sub-cadenas. Sin embargo, eso no quiere decir que no puedas tenerlos si estás preparado para extender mysql con funciones definidas por el usuario:

https://github.com/mysqludf/lib_mysqludf_preg

Eso puede no ser de mucha ayuda si desea distribuir su software, ya que es un impedimento para instalar su software, pero para una solución interna puede ser apropiado.

Answer 2

De acuerdo con http://dev.mysql.com/ la función SUBSTRING usa la posición de inicio y luego la longitud, así que seguramente la función para la segunda palabra sería:

SUBSTRING(sentence,LOCATE('' '',sentence),(LOCATE('' '',LOCATE('' '',sentence))-LOCATE('' '',sentence)))

Answer 3

El valor del campo es:

 "- DE-HEB 20% - DTopTen 1.2%"
SELECT ....
SUBSTRING_INDEX(SUBSTRING_INDEX(DesctosAplicados, ''DE-HEB '',  -1), ''-'', 1) DE-HEB ,
SUBSTRING_INDEX(SUBSTRING_INDEX(DesctosAplicados, ''DTopTen '',  -1), ''-'', 1) DTopTen ,

FROM TABLA 

El resultado es:

  DE-HEB       DTopTEn
    20%          1.2%

Answer 4

La siguiente es una solución propuesta para el problema específico de OP (extracción de la 2ª palabra de una cadena), pero debe tenerse en cuenta que, como lo dice la respuesta de mc0e, en realidad la extracción de coincidencias de expresiones regulares no se admite de fábrica en MySQL. Si realmente necesita esto, entonces sus opciones son básicamente: 1) hacerlo en postprocesamiento en el cliente, o 2) instalar una extensión MySQL para soportarlo.

BenWells lo tiene casi correcto. Trabajando desde su código, aquí hay una versión ligeramente ajustada:

SUBSTRING(
  sentence,
  LOCATE('' '', sentence) + CHAR_LENGTH('' ''),
  LOCATE('' '', sentence,
  ( LOCATE('' '', sentence) + 1 ) - ( LOCATE('' '', sentence) + CHAR_LENGTH('' '') )
)

Como ejemplo de trabajo, utilicé:

SELECT SUBSTRING(
  sentence,
  LOCATE('' '', sentence) + CHAR_LENGTH('' ''),
  LOCATE('' '', sentence,
  ( LOCATE('' '', sentence) + 1 ) - ( LOCATE('' '', sentence) + CHAR_LENGTH('' '') )
) as string
FROM (SELECT ''THIS IS A TEST'' AS sentence) temp

Esto extrae con éxito la palabra IS

Answer 5

Mi función de reemplazo de expresión regular de origen local se puede utilizar para esto.

Manifestación

Vea esta demostración de DB-Fiddle , que devuelve la segunda palabra ("I") de un soneto famoso y el número de apariciones (1).

SQL

Suponiendo que MySQL 8 o posterior se está utilizando (para permitir el uso de una Expresión de tabla común ), lo siguiente devolverá la segunda palabra y el número de ocurrencias de la misma:

WITH cte AS (
     SELECT digits.idx,
            SUBSTRING_INDEX(SUBSTRING_INDEX(words, ''~'', digits.idx + 1), ''~'', -1) word
     FROM
     (SELECT reg_replace(UPPER(txt),
                         ''[^''''’a-zA-Z-]+'',
                         ''~'',
                         TRUE,
                         1,
                         0) AS words
      FROM tbl) delimited
     INNER JOIN
     (SELECT @row := @row + 1 as idx FROM 
      (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t1,
      (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t2, 
      (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t3, 
      (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t4, 
      (SELECT @row := -1) t5) digits
     ON LENGTH(REPLACE(words, ''~'' , '''')) <= LENGTH(words) - digits.idx)
SELECT c.word,
       subq.occurrences
FROM cte c
LEFT JOIN (
  SELECT word,
         COUNT(*) AS occurrences
  FROM cte
  GROUP BY word
) subq
ON c.word = subq.word
WHERE idx = 1; /* idx is zero-based so 1 here gets the second word */

Explicación

Se usan algunos trucos en el SQL anterior y se necesita alguna acreditación. En primer lugar, el sustituto de expresión regular se utiliza para reemplazar todos los bloques continuos de caracteres que no sean palabras, cada uno de los cuales se reemplaza por un único carácter tilda ( ~ ). Nota: En su lugar, podría elegirse un personaje diferente si existe la posibilidad de que aparezca una tilda en el texto.

La técnica de esta respuesta se usa luego para transformar una cadena con valores delimitados en valores de fila separados. Se combina con la técnica inteligente de esta respuesta para generar una tabla que consiste en una secuencia de números crecientes: 0 - 10,000 en este caso.

Answer 6

No creo que tal cosa sea posible. Puede usar la función de subcadena para extraer la parte que desea.

Answer 7

No, no hay una sintaxis para extraer texto usando expresiones regulares. Tienes que usar las funciones de manipulación de cadenas ordinarias.

Alternativamente, seleccione el valor completo de la base de datos (o los primeros n caracteres si le preocupa demasiada transferencia de datos) y luego use una expresión regular en el cliente.

Answer 8

Opción más corta para extraer la segunda palabra en una oración:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(''THIS IS A TEST'', '' '',  2), '' '', -1) as FoundText

Documentos de MySQL para SUBSTRING_INDEX

Answer 9

Utilicé la respuesta de Brendan Bullen como punto de partida para un problema similar que tuve que era recuperar el valor de un campo específico en una cadena JSON. Sin embargo, como comenté su respuesta, no es del todo exacto. Si su límite izquierdo no es solo un espacio como en la pregunta original, entonces la discrepancia aumenta.

Solución corregida:

SUBSTRING(
    sentence,
    LOCATE('' '', sentence) + 1,
    LOCATE('' '', sentence, (LOCATE('' '', sentence) + 1)) - LOCATE('' '', sentence) - 1
)

Las dos diferencias son +1 en el parámetro de índice SUBSTRING y -1 en el parámetro de longitud.

Para una solución más general para "encontrar la primera aparición de una cadena entre dos límites proporcionados":

SUBSTRING(
    haystack,
    LOCATE(''<leftBoundary>'', haystack) + CHAR_LENGTH(''<leftBoundary>''),
    LOCATE(
        ''<rightBoundary>'',
        haystack,
        LOCATE(''<leftBoundary>'', haystack) + CHAR_LENGTH(''<leftBoundary>'')
    )
    - (LOCATE(''<leftBoundary>'', haystack) + CHAR_LENGTH(''<leftBoundary>''))
)