Gerado o Oracle 10
--- Criar máscara
SELECT
Decode(cpf, NULL,NULL,
Translate(To_Char(cpf/100
,'000,000,000.00')
,',.'
,'.-')) cpf_com_mascara,
Decode(cnpj,NULL,NULL,
REPLACE(REPLACE(REPLACE(To_Char(LPad(REPLACE(cnpj,' ')
,14
,'0')
,'00,000,000,0000,00')
,','
,'.')
,' ')
,'.'||Trim(To_Char(Trunc(Mod(LPad(cnpj
,14
,'0')
,1000000)/100)
,'0000'))||'.'
,'/'||Trim(To_Char(Trunc(Mod(LPad(cnpj
,14
,'0')
,1000000)/100)
,'0000'))||'-')) cnpj_com_mascara
FROM (
(SELECT '12345678912' cpf, '12345678000189' cnpj FROM dual))
-- Retirar a máscara
SELECT REPLACE(Translate('012.365.478-56','.-',' '),' ') FROM dual;
SELECT REPLACE(Translate('12.365.478/0001-56','./-',' '),' ') FROM dual;