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

[flebrao]

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

[dr_gori]

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> 

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> 

[flebrao]

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


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

Abraços
Fábio Lebrão

[mprocha]

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

[Junior_life]

Parabens pessoal.. me ajudou muito..

[nozes]

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!

[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]

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

[Porva]

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]

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.

[dr_gori]

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