Mysql - Search number in a string

Asked

Viewed 1,911 times

3

SGBD: Mysql

I have a table with a column of the type Varchar, with the following data: 

id   Descrição
1    Pgto NF 66096 Lg Sistemas vcto: 01/10/2016.
2    Pgto NF 66096 Lg Sistemas vcto: 01/10/2016.
3    Pgto NF 1763 Criare Net vcto: 02/10/2016.
4    Pgto NF 1763 Criare Net vcto: 02/10/2016.
5    Pgto NF 1 Criare Net vcto: 02/10/2016.
6    Pgto NF 1 Criare Net vcto: 02/10/2016.
7    Pgto Fundo Fixo Ordem de Pagamneto 871159 Adria de Jesus vcto: 03/10/2016.
8    Pgto Fundo Fixo Ordem de Pagamneto 871159 Adria de Jesus vcto: 03/10/2016.
9    Pgto Tarifa Bancária  Ordem de  Pagamento 057-871159.
10   Pgto Tarifa Bancária  Ordem de  Pagamento 057-871159.
11   [Serviço Técnico de Terceiros em  Treinamento Tecnologia LED; Exper Soluçoes Luminotecas EIRELI; NF 166]  vcto: 17/10/2016.
12   [Serviço Técnico de Terceiros em  Treinamento Tecnologia LED; Exper Soluçoes Luminotecas EIRELI; NF 166]  vcto: 17/10/2016.
13   Pgto NF 2067 Interface Recursos Humanos LTDA vcto: 03/10/2016.
14   Pgto NF 2067 Interface Recursos Humanos LTDA vcto: 03/10/2016.
15   [Vale Transporte; Mês 10/16] Pagamento ao Estagiários Leandro, Bruno,  Kayo e Caio Fatura 1712317 vcto:03/10/09/2016.
16   [Vale Transporte; Mês 10/16] Pagamento ao Estagiários Leandro, Bruno,  Kayo e Caio Fatura 1712317 vcto:03/10/09/2016.
17   [Vale Transporte; Mês 10/16] Pagamento ao Estagiários Leandro, Bruno,  Kayo e Caio Fatura 1712317 vcto:03/10/09/2016.

I need to perform a Select that extracts only the Invoice (NF) numbers and service/payment orders. The problem is nonexistent standard of how the data is positioned in the string, because it will not always be written "NF" or "Payment Order" to perform the search. Select must be in a way that selects only the numbers of the bills and service/payment orders, without bringing other data type dates or names, as shown below:

Expected result:

id   Descrição
1    66096
2    66096
3    1763
4    1763
5    1
6    1    
7    871159
8    871159
9    057-871159
10   057-871159
11   166
12   166
13   2067
14   2067
15   1712317
16   1712317
17   1712317

Can someone help me?

Thanks!

mysql sql string

  • You’ll need at least one trial to do that. And look there. Chances are you’ll have to gradually standardize this with replace and then the process will be able to extract the data just the way you want it.

2 answers

2

Try it this way:

CREATE FUNCTION IsNumeric (val varchar(255)) 
RETURNS tinyint 
RETURN val REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';

CREATE FUNCTION NumericOnly (val VARCHAR(255)) 
RETURNS VARCHAR(255)
BEGIN
DECLARE idx INT DEFAULT 0;
IF ISNULL(val) THEN RETURN NULL; END IF;

IF LENGTH(val) = 0 THEN RETURN ""; END IF;

SET idx = LENGTH(val);
WHILE idx > 0 DO
IF IsNumeric(SUBSTRING(val,idx,1)) = 0 THEN
SET val = REPLACE(val,SUBSTRING(val,idx,1),"");
SET idx = LENGTH(val)+1;
END IF;
SET idx = idx - 1;
END WHILE;
RETURN val;
END;

Ai calls the function so:

select NumericOnly('1&2') as result;

Result: "12"

But seeing here I think you’ll have to adapt a little because of the dates that also contain numbers

Source: https://stackoverflow.com/questions/21068063/mysql-query-will-remove-characters-from-a-string

1

Mysql

SELECT * FROM tabela
WHERE campo REGEXP '^[0-9]+$';

SQL Server:

SELECT LEFT(subtexto, PATINDEX('%[^0-9]%', subtexto + 'texto') - 1) FROM (
SELECT subtexto = SUBSTRING(Nome_Campo, pos, LEN(Nome_campo)) FROM (
    SELECT Nome_Campo, pos = PATINDEX('%[0-9]%', Nome_campo) FROM Nome_Tabela) e) texto

Browser other questions tagged mysql sql string

You are not signed in. Login or sign up in order to post.