Format Chilean Rut in Postgresql
Here is a very practical method to format Chilean Rut (xx.xxx.xxx-x)
The only requirement is to have the following function previously.
PadIndex Postgresql
CREATE OR REPLACE FUNCTION public.fn_format_rut(var_rut varchar(12))
RETURNS varchar(12) as $
declare
v_cont integer;
v_i integer;
v_new_rut VARCHAR(12);
begin
v_cont := 0;
v_i := 0;
var_rut := substring(var_rut, patindex('%[^0]%',var_rut || '.'), length(var_rut));
if(length(var_rut) = 0) then
return '';
else
var_rut := replace(var_rut::text, '.','');
var_rut := replace(var_rut::text, '-','');
v_new_rut := '-' || right(var_rut::text, 1);
v_i := length(var_rut);
while v_i >= 2 loop
v_i := v_i -1;
v_new_rut := substring(var_rut, v_i, 1) || v_new_rut;
v_cont := v_cont + 1;
if(v_cont = 3 and v_i <> 0) then
v_new_rut := '.' || v_new_rut;
v_cont := 0;
end if;
end loop;
end if;
return v_new_rut;
END;
$ language 'plpgsql';