Aprenda PL/SQL

Função Para varrer String e deixar só números

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Mensagemem Sex, 09 Fev 2007 10:17 am

Bom dia Pessoal, alguém por acaso tem alguma função que varre uma string e so me retorna os numeros da mesma? ex AAAAA23659BBBBB
retorna somente 23659



Abraços
Fábio
flebrao
Localização: Santa Bárbara D'Oeste

Mensagemem Sex, 09 Fev 2007 11:04 am

Veja a idéia que eu tive agora: (acompanhe meu raciocínio)
A função TRANSLATE altera todas ocorrencias de um determinado número para outro.
Daí eu fiz o seguinte teste, veja minha tabela:
Código: Selecionar todos
SQL> select endereco from enderecopessoa where rownum < 10
  2  /

ENDERECO
----------------------------------------
AV SERTORIO, 6600
AV SERTORIO, 6600 SOBRELOJA
RUA BOM JESUS, 159
DESATIVADA
DESATIVADA
DESATIVADA
RUA SERGIO DIETRICH, S/N
DESATIVADA
AV PARAGUACU, 2578

9 rows selected

SQL>


Rodei o translate pra retirar todos os números dos meus dados. Com isso, eu tenho todas as letras que NÃO ME INTERESSAM.
Código: Selecionar todos
SQL> select translate(ENDERECO, '1234567890', ' ')
  2  from enderecopessoa where rownum < 10
  3  /

TRANSLATE(ENDERECO,'1234567890
----------------------------------------
AV SERTORIO,
AV SERTORIO,  SOBRELOJA
RUA BOM JESUS,
DESATIVADA
DESATIVADA
DESATIVADA
RUA SERGIO DIETRICH, S/N
DESATIVADA
AV PARAGUACU,

9 rows selected

SQL>


Agora, eu faço um translate novamente, pra RETIRAR essas letras acima. Com isso, vai sobrar apenas os números:
Código: Selecionar todos
SQL> select LTRIM(translate(ENDERECO, translate(ENDERECO, '1234567890', ' ') , ' '))
  2  from enderecopessoa where rownum < 10
  3  /

LTRIM(TRANSLATE(ENDERECO,TRANS
----------------------------------------
6600
6600
159
2578

9 rows selected

SQL>


:-D

Vamos agora fazer um exemplo com uma string bem mais complicada:
Código: Selecionar todos
SQL> SELECT '65 HG5IS 258 KS34 LKJ4 AS 21DF G AÇ K43 ' ENDERECO FROM DUAL
  2  /

ENDERECO
----------------------------------------
65 HG5IS 258 KS34 LKJ4 AS 21DF G AÇ K43

SQL>
SQL> select LTRIM(translate(ENDERECO, translate(ENDERECO, '1234567890', ' ') , ' ')) SO_NUMEROS
  2  from (SELECT '65 HG5IS 258 KS34 LKJ4 AS 21DF G AÇ K43 ' ENDERECO FROM DUAL)
  3 
SQL> /

SO_NUMEROS
-------------
6552583442143

SQL>


:-o
dr_gori
Localização: Porto Alegre - RS

Thomas F. G
https://www.patreon.com/glufke

Mensagemem Sex, 09 Fev 2007 2:42 pm

Nem preciso dizer nada... hehehe funciono perfeitamente ... este forum e uma verdadeira escola...


Muito Obrigado Dr_gori pela sugestão...
você é o froide... :-o

Abraços
Fábio Lebrão
flebrao
Localização: Santa Bárbara D'Oeste

Mensagemem Qua, 28 Jan 2009 9:52 am

E o inverso tambem, Só letras

Código: Selecionar todos
SQL> SELECT '65 HG5IS 258 KS34 LKJ4 AS 21DF G AÇ K43 ' ENDERECO FROM DUAL;

ENDERECO
----------------------------------------
65 HG5IS 258 KS34 LKJ4 AS 21DF G AÇ K43

SQL>
SQL> select replace ( translate(ENDERECO, '1234567890', ' ') , ' ', '') SO_LETRAS
  2       from (SELECT '65 HG5IS 258 KS34 LKJ4 AS 21DF G AÇ K43 ' ENDERECO FROM DUAL)
  3  /

SO_LETRAS
-----------------
HGISKSLKJASDFGAÇK

8)
mprocha
Localização: Campinas - SP

Marcos Pinheiro da Rocha
Desenvolvedor Java/Forms/PL-SQL

Mensagemem Qua, 24 Fev 2010 2:44 pm

Parabens pessoal.. me ajudou muito..
Junior_life
Localização: Toledo

Mensagemem Qui, 23 Dez 2010 2:12 pm

Opa..eae galera...meu primeiro post...to sem projeto e achei q posso contribuir como vocês vem contribuindo comigo, a muito tempo!!!!

Tava lendo as duvidas e sugestões acima...e tenho uma solução também...ai vai da necessidade e qual solução vocês querem adotar......

Para você ter qualquer numero da tua string retirando as letras, utilize esta função...
Código: Selecionar todos
TRANSLATE(UPPER([variavel]),'0123456789,./-ABCDEFGHIJKLMNOPQRSTUVWXYZÇ','0123456789')

Caso precise de ponto ou virgula, só retirar do segundo argumento da função.

apesar de atrasado, espero ter ajudado!!!
Grande Abraço! :-o
nozes
Localização: Mesquita-RJ

Emanuel "Nozes" Castro
Analista Programador

Mensagemem Qui, 23 Dez 2010 3:51 pm

Segue outra maneira utilizando expressões regulares.....

Código: Selecionar todos

select regexp_replace('65 HG5IS 258 KS34 LKJ4 AS 21DF G AÇ K43 ','[^[:digit:]]') resultado_da_query from dual;

--------------------------------------
RESULTADO_DA_QUERY
6552583442143

paulochagas
Localização: São Paulo - SP

Paulo Chagas Filho
__________________

Analista Funcional / Desenvolvedor Oracle EBS
MSN - paulochagas@hotmail.com
Gtalk - pachafi@gmail.com
Skype - paulochagas

Mensagemem Qua, 26 Ago 2015 6:01 pm

Neste caso creio que pode ser usado da seguinte forma, assim já retira letras e caracteres especiais.

Código: Selecionar todos
WITH teste AS
  (
   SELECT 'rua sei lá, Número 0072' AS endereco FROM dual
  )
SELECT to_number(REGEXP_REPLACE(teste.endereco, '[^[:digit:]]')) AS alteracao
FROM teste
JeanParro

Mensagemem Qua, 06 Abr 2016 11:25 am

Usei a função Translate conforme indicado acima, pois alguns registros contém sujeiras no campo que deveria conter apenas números, pontos ou vírgulas, e sinal de negativo, alguém saber dizer o porquê do primeiro Select retorna valor:

Código: Selecionar todos
SELECT LTRIM(translate('97790.41', translate('97790.41', '1234567890-.,', ' ') , ' ')) VALOR FROM dual;



mas o segundo não!:

Código: Selecionar todos
SELECT LTRIM(translate('27360.66', translate('27360.66', '1234567890-.,', ' ') , ' ')) VALOR FROM dual;



:? :? :? :? :? :? :?
Porva
Localização: São Paulo/SP

Rafael S. Nunes
São Paulo/SP

Mensagemem Qua, 11 Jan 2017 3:53 pm

Efetuando o comando do colega, temos dois resultados diferentes para o mesmo comando:
Código: Selecionar todos
SELECT LTRIM(translate('97790.41', translate('97790.41', '1234567890-.,', ' ') , ' ')) VALOR FROM dual;

VALOR
--------
97790.41
Código: Selecionar todos
SELECT LTRIM(translate('27360.66', translate('27360.66', '1234567890-.,', ' ') , ' ')) VALOR FROM dual;

VALOR
--------
null
Mas executando este, temos o mesmo resultado sempre:
Código: Selecionar todos
WITH teste AS
(
SELECT '27360.66' AS VAR FROM dual
)
SELECT to_number(REGEXP_REPLACE(teste.VAR, '[^[:digit:]]')) AS alteracao
FROM teste;

ALTERACAO
----------
2736066

Como faço para deixar apenas LETRAS e NUMEROS?
Código: Selecionar todos
WITH teste AS
(
SELECT 'AA47.213-5' AS VAR FROM dual
)
SELECT to_number(REGEXP_REPLACE(teste.VAR, '[^[:digit:]]')) AS alteracao
FROM teste;

ALTERACAO
----------
472135
Tenho que retornar AA472135.
oliveiramonica

Mensagemem Qua, 11 Jan 2017 4:36 pm

Faça assim:
Código: Selecionar todos
WITH teste AS
(
SELECT ' AA 47.213-5## X' AS VAR FROM dual
)
SELECT var, REGEXP_REPLACE(teste.VAR, '[^[:alnum:]]') AS alteracao
FROM teste;


Exemplo:
Código: Selecionar todos
SQL> WITH teste AS
  2  (
  3  SELECT ' AA 47.213-5## X' AS VAR FROM dual
  4  )
  5  SELECT var, REGEXP_REPLACE(teste.VAR, '[^[:alnum:]]') AS alteracao
  6  FROM teste;

VAR              ALTERACAO
---------------- ----------------
AA 47.213-5## X AA472135X

SQL>


Aqui tem uma lista de todos que você pode usar:
[:alnum:] Alphanumeric characters
[:alpha:] Alphabetic characters
[:blank:] Blank Space Characters
[:cntrl:] Control characters (nonprinting)
[:digit:] Numeric digits
[:graph:] Any [:punct:], [:upper:], [:lower:], and [:digit:] chars
[:lower:] Lowercase alphabetic characters
[:print:] Printable characters
[:punct:] Punctuation characters
[:space:] Space characters (nonprinting), such as carriage return, newline, vertical tab, and form feed
[:upper:] Uppercase alphabetic characters
[:xdigit:] Hexidecimal characters


http://psoug.org/reference/regexp.html
:-o
dr_gori
Localização: Porto Alegre - RS

Thomas F. G
https://www.patreon.com/glufke


  • Veja também
    Respostas
    ExibiÇões
    Última mensagem