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
Responder
flebrao
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 116
Registrado em: Seg, 10 Abr 2006 4:46 pm
Localização: Santa Bárbara D'Oeste

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
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

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:

Selecionar tudo

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.

Selecionar tudo

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:

Selecionar tudo

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:

Selecionar tudo

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
flebrao
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 116
Registrado em: Seg, 10 Abr 2006 4:46 pm
Localização: Santa Bárbara D'Oeste

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
mprocha
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 19
Registrado em: Sex, 11 Jul 2008 11:14 am
Localização: Campinas - SP
Marcos Pinheiro da Rocha
Desenvolvedor Java/Forms/PL-SQL

E o inverso tambem, Só letras

Selecionar tudo

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)
Junior_life
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Mensagens: 1
Registrado em: Qua, 24 Fev 2010 2:42 pm
Localização: Toledo

Parabens pessoal.. me ajudou muito..
nozes
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 10
Registrado em: Qui, 23 Dez 2010 2:06 pm
Localização: Mesquita-RJ
Emanuel "Nozes" Castro
Analista Programador

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...

Selecionar tudo

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
paulochagas
Moderador
Moderador
Mensagens: 86
Registrado em: Qua, 15 Mar 2006 2:46 pm
Localização: São Paulo - SP
Paulo Chagas Filho
__________________

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

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

Selecionar tudo


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

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

JeanParro
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Mensagens: 1
Registrado em: Qua, 26 Ago 2015 5:43 pm

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

Selecionar tudo

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
Avatar do usuário
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Mensagens: 342
Registrado em: Seg, 29 Jan 2007 7:36 am
Localização: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

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:

Selecionar tudo

SELECT LTRIM(translate('97790.41', translate('97790.41', '1234567890-.,', ' ') , ' ')) VALOR FROM dual;

mas o segundo não!:

Selecionar tudo

SELECT LTRIM(translate('27360.66', translate('27360.66', '1234567890-.,', ' ') , ' ')) VALOR FROM dual;

:? :? :? :? :? :? :?
oliveiramonica
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Mensagens: 1
Registrado em: Qua, 11 Jan 2017 3:44 pm

Efetuando o comando do colega, temos dois resultados diferentes para o mesmo comando:

Selecionar tudo

SELECT LTRIM(translate('97790.41', translate('97790.41', '1234567890-.,', ' ') , ' ')) VALOR FROM dual;
VALOR
--------
97790.41

Selecionar tudo

SELECT LTRIM(translate('27360.66', translate('27360.66', '1234567890-.,', ' ') , ' ')) VALOR FROM dual;
VALOR
--------
null
Mas executando este, temos o mesmo resultado sempre:

Selecionar tudo

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?

Selecionar tudo

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.
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Faça assim:

Selecionar tudo

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:

Selecionar tudo

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
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 10 visitantes