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
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>
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>
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>
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>
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
TRANSLATE(UPPER([variavel]),'0123456789,./-ABCDEFGHIJKLMNOPQRSTUVWXYZÇ','0123456789')
select regexp_replace('65 HG5IS 258 KS34 LKJ4 AS 21DF G AÇ K43 ','[^[:digit:]]') resultado_da_query from dual;
--------------------------------------
RESULTADO_DA_QUERY
6552583442143
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
SELECT LTRIM(translate('97790.41', translate('97790.41', '1234567890-.,', ' ') , ' ')) VALOR FROM dual;
SELECT LTRIM(translate('27360.66', translate('27360.66', '1234567890-.,', ' ') , ' ')) VALOR FROM dual;
SELECT LTRIM(translate('97790.41', translate('97790.41', '1234567890-.,', ' ') , ' ')) VALOR FROM dual;
SELECT LTRIM(translate('27360.66', translate('27360.66', '1234567890-.,', ' ') , ' ')) VALOR FROM dual;
WITH teste AS
(
SELECT '27360.66' AS VAR FROM dual
)
SELECT to_number(REGEXP_REPLACE(teste.VAR, '[^[:digit:]]')) AS alteracao
FROM teste;
WITH teste AS
(
SELECT 'AA47.213-5' AS VAR FROM dual
)
SELECT to_number(REGEXP_REPLACE(teste.VAR, '[^[:digit:]]')) AS alteracao
FROM teste;
WITH teste AS
(
SELECT ' AA 47.213-5## X' AS VAR FROM dual
)
SELECT var, REGEXP_REPLACE(teste.VAR, '[^[:alnum:]]') AS alteracao
FROM teste;
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>
[: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
Usuários navegando neste fórum: Bing [Bot] e 4 visitantes