/*
select calle, numero, '' from proveedores_domicilio;


-- calles con nombre de numeros
SELECT calle , substring(calle FROM '^[0-9]+')
 from proveedores_domicilio 
where cast(substring(calle FROM '^[0-9]+') as integer) > 1
 order by 2;


 select calle, substring(calle FROM '^[a-zA-Z]+'), 
 position(substring(calle FROM '[0-9]+') in calle) , length(calle), 
 substring(calle FROM '\w+\d+[-][\d\w]+')
 , substring(calle FROM '\w+\d+')
 ,trim ( substring(replace(calle, 'NO.','') , 0, position(substring(replace(calle, 'NO.','') FROM '[0-9]+') in replace(calle, 'NO.',''))) )
 --, case 
 , substring(calle FROM '[a-zA-Z]+')

, REPLACE(substring(calle, 0, position(substring(calle FROM '\s((\d+[-][\d+\w+]+)|(\d+))') in calle)), 'NO.', '')
, substring(calle from '\s((\d+[-][\d+\w+]+)|(\d+))')
 
 from proveedores_domicilio 
 where upper(calle) not like '%S/N%' and upper(calle) not like '%SIN N_MERO%' and upper(calle) not like '%NO CUENTA%' 
 order by 7

 --SELECT substring('123 Main Street' FROM '^[0-9]+')
;

*/
 SELECT id, calle, split_part(calle, ' ', 1) AS col1
      ,split_part(calle, ' ', 2) AS col2
      ,split_part(calle, ' ', 3) AS col3
      ,split_part(calle, ' ', 4) AS col4
      ,split_part(calle, ' ', 5) AS col5
      ,split_part(calle, ' ', 6) AS col6
      ,split_part(calle, ' ', 7) AS col7
      ,split_part(calle, ' ', 8) AS col8
      ,split_part(calle, ' ', 9) AS col9
      ,split_part(calle, ' ', 10) AS col10
FROM   proveedores_domicilio
where upper(calle) not like '%S/N%' and upper(calle) not like '%SIN N_MERO%' and upper(calle) not like '%NO CUENTA%' and upper(calle) not like '%"NO CEUNTA"%' 
and upper(calle) not like '%""SIN CALLE""%' 
order by 10,9,8,7,6,5,4,3,2,1
;


SELECT *,

position('NO.' in calle)

, trim(substring (calle, 0, position('NO.' in calle))) as nueva_calle 
, trim(substring (calle, position('NO.' in calle)+3, length(calle))) as nuevo_numero

from proveedores_domicilio
where numero is null 
and position('NO.' in calle) > 1
order by 1 desc;


update proveedores_domicilio
set calle = trim(substring (b.calle, 0, position('NO.' in b.calle))), numero  =trim(substring (b.calle, position('NO.' in b.calle)+3, length(b.calle)))
from proveedores_domicilio b
where b.numero is null 
and position('NO.' in b.calle) > 1
and proveedores_domicilio.id = b.id
;

select * from proveedores_domicilio where numero is null ;

SELECT *,
position('NUM.' in calle)
, trim(substring (calle, 0, position('NUM.' in calle))) as nueva_calle 
, trim(substring (calle, position('NUM.' in calle)+4, length(calle))) as nuevo_numero
from proveedores_domicilio
where numero is null 
and position('NUM.' in calle) > 1
order by 1 desc;

update proveedores_domicilio
set calle = trim(substring (b.calle, 0, position('NUM.' in b.calle))), numero  =trim(substring (b.calle, position('NUM.' in b.calle)+3, length(b.calle)))
from proveedores_domicilio b
where b.numero is null 
and position('NUM.' in b.calle) > 1
and proveedores_domicilio.id = b.id;


SELECT *,
position(' NO ' in calle)
, trim(substring (calle, 0, position(' NO ' in calle))) as nueva_calle 
, trim(substring (calle, position(' NO ' in calle)+length(' NO '), length(calle))) as nuevo_numero
from proveedores_domicilio
where numero is null 
and position(' NO ' in calle) > 1
order by 1 desc;

update proveedores_domicilio
set calle = trim(substring (b.calle, 0, position(' NO ' in b.calle))), numero  =trim(substring (b.calle, position(' NO ' in b.calle)+length(' NO '), length(b.calle)))
from proveedores_domicilio b
where b.numero is null 
and position(' NO ' in b.calle) > 1
and proveedores_domicilio.id = b.id;


select * from proveedores_domicilio where numero is null
AND CALLE <> 'NO CUENTA' 
and calle not like '%S/N%'
;