Função MySQL REGEXP_INSTR()

Resumo : neste tutorial, você aprenderá como usar a REGEXP_INSTR()função MySQL para obter a posição inicial de uma substring que corresponde a uma expressão regular dentro de uma string de entrada.

Introdução à função MySQL REGEXP_INSTR()

A REGEXP_INSTR()função retorna a posição inicial de uma substring que corresponde a um padrão de expressão regular dentro de uma determinada string.

Aqui está a sintaxe básica da REGEX_INSTR()função:

REGEXP_INSTR(
   expression, 
   pattern, 
   position,
   occurrence,
   return_option,
   match_type
)Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Nesta sintaxe:

• expression: a string de entrada onde você deseja pesquisar o padrão.
• pattern: o padrão de expressão regular a ser correspondido.
• position: a posição na expressão na qual a função inicia a pesquisa. O padrão da posição é 1, que está no início da string.
• occurrence: este parâmetro determina qual ocorrência de uma correspondência procurar. O padrão é 1 se você o omitir.
• return_option: retorna a posição do primeiro caractere da substring correspondente se for return_option0. Retorna a posição após a substring correspondente se for return_option1. O return_optionpadrão é 0.
• match_type: Esta é uma string que instrui como a função deve realizar a correspondência.

O match_typepode conter qualquer um ou todos os seguintes caracteres que especificam como a função executa a correspondência:

Tipo de partida	Descrição
c	Correspondência com distinção entre maiúsculas e minúsculas
eu	Correspondência sem distinção entre maiúsculas e minúsculas
eu	Modo de múltiplas linhas
n	Por padrão, o “.” caractere corresponderá a qualquer caractere em uma única linha e parará no final dessa linha. Se match_typefor n, corresponderá aos terminadores de linha.
você	O sinalizador “u” significa finais de linha somente Unix. Este sinalizador modifica o comportamento de determinados operadores de correspondência de expressões regulares, especificamente os operadores “.”, “^” e “$”. Quando você usa o sinalizador “u”, esses operadores tratam apenas o caractere de nova linha (geralmente representado como “\n”) como um final de linha reconhecido.

Se a expressão ou padrão for NULL, a REGEXP_INSTR()função retornará NULL.

Exemplos de funções MySQL REGEXP_INSTR()

Vejamos alguns exemplos de uso da REGEXP_INSTR()função

1) Exemplo simples de função MySQL REGEXP_INSTR()

O exemplo a seguir usa a REGEXP_INSTRfunção para obter a posição inicial de uma sequência de dígitos:

SELECT 
  REGEXP_INSTR(
    '1936 Mercedes-Benz 500K Special Roadster', 
    '\\d+'
  ) position;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+----------+
| position |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

O padrão \\d+corresponde a um ou mais dígitos. Portanto, a REGEX_INSTRfunção retorna a posição 1, que é a posição inicial da string correspondente 1936.

2) Exemplo de posição

Por padrão, a REGEX_INSTR()pesquisa começa no início da string. No entanto, você pode alterar esse comportamento especificando o parâmetro position.

Por exemplo, a consulta a seguir usa a REGEXP_INSTRfunção para procurar uma sequência de dígitos começando na posição 5:

SELECT 
  REGEXP_INSTR(
    '1936 Mercedes-Benz 500K Special Roadster', 
    '\\d+', 5
  ) position;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+----------+
| position |
+----------+
|       20 |
+----------+
1 row in set (0.00 sec)Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Ele retorna a posição 20, que é a posição inicial da substring 500.

3) Exemplo de ocorrência

Para encontrar a posição de uma correspondência específica, você pode usar o parâmetro de ocorrência. Por exemplo, a consulta a seguir retorna as posições da primeira e da segunda correspondências:

SELECT 
  REGEXP_INSTR(
    '1936 Mercedes-Benz 500K Special Roadster', 
    '\\d+', 1, 1
  ) first_match, 
  REGEXP_INSTR(
    '1936 Mercedes-Benz 500K Special Roadster', 
    '\\d+', 1, 2
  ) second_match;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+-------------+--------------+
| first_match | second_match |
+-------------+--------------+
|           1 |           20 |
+-------------+--------------+
1 row in set (0.00 sec)Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

4) exemplo de opção de retorno

Por padrão, a posição inicial da correspondência é o início da substring que corresponde à expressão regular.

Por exemplo, REGEXP_INSTRretorna 1 para a correspondência 1936 porque o primeiro dígito 1 em 1936 tem a posição 1 na sequência de entrada.

Se quiser obter a posição após o dígito 6 da substring, você pode alterar return_optionpara 1:

SELECT 
  REGEXP_INSTR(
    '1936 Mercedes-Benz 500K Special Roadster', 
    '\\d+', 1, 1, 1
  ) first_match;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+-------------+
| first_match |
+-------------+
|           5 |
+-------------+
1 row in set (0.00 sec)Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

5) exemplo match_type

Por padrão, a REGEXPcorrespondência não diferencia maiúsculas de minúsculas. Se quiser corresponder um padrão com distinção entre maiúsculas e minúsculas, você pode usar a match_typeopção 'c':

SELECT 
  REGEXP_INSTR(
    '1936 Mercedes-Benz 500K Special Roadster', 
    '\\d+k', 1, 1, 1, 'c'
  ) position;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+----------+
| position |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Neste exemplo, o padrão \\d+kcorresponde a um ou mais dígitos seguidos pela letra k (minúscula).

Mas o match_typec instrui a REGEXP_INSTR()função a fazer a correspondência com distinção entre maiúsculas e minúsculas, portanto, a função não encontra correspondência. Como resultado, ele retorna 0.

6) Usando a função REGEXP_INSTR() com dados da tabela

Usaremos a productstabela do banco de dados de exemplo :

A consulta a seguir usa a REGEX_INSTR()função para encontrar a posição da substring de 4 dígitos que corresponde ao padrão \\d{4}:

SELECT 
  productName 
FROM 
  products 
WHERE 
  REGEXP_INSTR(productName, '\\d{4}') > 0;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Aqui está a saída parcial:

+---------------------------------------------+
| productName                                 |
+---------------------------------------------+
| 1969 Harley Davidson Ultimate Chopper       |
| 1952 Alpine Renault 1300                    |
| 1996 Moto Guzzi 1100i                       |
| 2003 Harley-Davidson Eagle Drag Bike        |
| 1972 Alfa Romeo GTA                         |
| 1962 LanciaA Delta 16V                      |
...Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Resumo

• Use a função MySQL REGEXP_INSTR()para obter a posição de uma substring que corresponde a uma expressão regular dentro de uma string.