kratos2333
2/28/2019 - 12:53 AM

Address Translation

Steps for the address translation

README.markdown
content_copyfile_download
  • Rendered
  • Source

Address translation

  1. Copy the two column in the excel file into a new txt file and removing the header (save as unicode)
  2. Go to aauteappsw01
  3. Copy txt file to E:\addrtest (remove heading, change encoding matching other jobs)
  4. Run address_eval_test.exe which will specify the txt file and output file
  5. Copy translated file to aautesqlpw01 d:\temp
  6. Change the name to date.out.txt
  7. Open reg10.sql
  8. Database: ute_c
  9. Control+h change all date to new date like 0227
  10. Follow the reg10.sql step by step
  11. Select the final table, and copy it to excel as the result
reg10.sql
content_copyfile_download

/****** Object:  Table [dbo].[20180502_Regaddress_20180627]    Script Date: 5/10/2018 12:00:02 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

drop table [Regaddress_20190219]
drop table [Regaddress_20190219v0]
go
CREATE TABLE [dbo].[Regaddress_20190219](
	[LK] [int] NULL,
	[Address] [nvarchar](450) NULL,
	[Addr_R] [varchar](500) NULL,
	[City_R] [varchar](50) NULL,
	[Province_R] [varchar](50) NULL,
	[Addr_ML] [varchar](500) NULL,
	[City_ML] [nvarchar](50) NULL,
	[Province_ML] [nvarchar](50) NULL,
	[Selected] [int] NULL
) ON [PRIMARY]

GO

select * from [dbo].[Regaddress_20190219]
--truncate table [Regaddress_20190219]
exec xp_cmdshell 'BCP UTE_C..Regaddress_20190219 in D:\Temp\0219.out.txt -T -w'
--BULK INSERT Regaddress_20190219 FROM 'D:\Temp\0219.out.txt' WITH (firstrow = 2, BATCHSIZE = 5000, CODEPAGE = 'RAW',DATAFILETYPE = 'widechar')

--alter table [Regaddress_20190219] add [FinalAddr] [varchar](500) NULL
select count(*) from [Regaddress_20190219]

select * from [Regaddress_20190219] where Addr_R is null and Selected = 1  --21

sp_rename [Regaddress_20190219], [Regaddress_20190219v0] 

--drop table [Regaddress_20190219v0]
--alter table [Regaddress_20190219v0] drop column FinalAddr
select *,iif(selected =1, Addr_R,  Addr_ML) as FinalAddr INTO [Regaddress_20190219]  from [Regaddress_20190219v0] --147475


select * from [Regaddress_20190219] where Province_ML is null and Province_R is not null -- 2177695 and charindex
select * from [Regaddress_20190219] where Province_ML is null and Province_R is not null and SUBSTRING(Address,3,1) <> N'省' and SUBSTRING(Address,3,1) not in (  N'市', N'县')
AND Selected = 2 --708
select * from [Regaddress_20190219] where Province_ML is null and Province_R is not null and SUBSTRING(Address,3,1) = N'省' --28

--遵化市团瓢庄乡洪水川村
SELECT *FROM [Regaddress_20190219] WHERE Selected =1 AND Addr_R IS NULL AND Addr_ML IS NOT NULL

select * from [Regaddress_20190219] WHERE Selected =1 AND Addr_R IS NULL AND Addr_ML IS NOT NULL
UPDATE [Regaddress_20190219] SET Selected =2 WHERE Selected =1 AND Addr_R IS NULL AND Addr_ML IS NOT NULL

select * from [Regaddress_20190219] WHERE Province_ML is null and Province_R is not null and SUBSTRING(Address,3,1) <> N'省' --50590
update [Regaddress_20190219] set  Province_R = null  WHERE Province_ML is null and Province_R is not null and SUBSTRING(Address,3,1) <> N'省'

update [Regaddress_20190219]  SET Province_R = NULL WHERE Province_R = City_R --52608
update [Regaddress_20190219]  SET Province_ML = NULL WHERE Province_ML = City_ML --52612
select * from [Regaddress_20190219]  WHERE FinalAddr = Province_R

update [Regaddress_20190219]  SET FinalAddr = NULL WHERE FinalAddr = Province_R
select * from [Regaddress_20190219]  WHERE Province_ML is null and  Province_R is  null --291825

update [Regaddress_20190219]  SET Province_ML = Province_R WHERE Province_ML is NULL --292000
select * from [Regaddress_20190219] WHERE SUBSTRING(Address,3,1) <> N'省' --296152

--ALTER TABLE [Regaddress_20190219] ADD FinalAddr VARCHAR(500)
UPDATE [Regaddress_20190219]  set FinalAddr = iif(selected =1, dbo.SetDisplay(dbo.SetDisplay(Addr_R,City_R),Province_R) ,  dbo.SetDisplay(dbo.SetDisplay(Addr_ML,City_ML),Province_ML)) --349699

select *  from [Regaddress_20190219]   where ISNULL(City_R,'*')  <> ISNULL(City_ML,'*')  order by LK --25066

select lk, address, isnull(finaladdr,'#####')  from [Regaddress_20190219] where FinalAddr is null   --2

select * from [Regaddress_20190219] where province_R in (N'Ningxia','Tibet','Xinjiang','Guangxi','Inner Mongolia')  and CHARINDEX (N'自治区', Address) = 0 --311



select lk as da, finaladdr as db, * from [Regaddress_20190219] where charindex(' ',finaladdr) = 0 AND charindex(' ',Addr_ML) > 0  order by finaladdr --36
UPDATE [Regaddress_20190219] SET FINALADDR = ADDR_ML WHERE LEN(FINALADDR) < 10 AND LEN(ADDR_ML) > 15  --32
UPDATE [Regaddress_20190219] SET FINALADDR = ADDR_ML WHERE charindex(' ',finaladdr) = 0 AND charindex(' ',Addr_ML) > 0  --8

SELECT * FROM [Regaddress_20190219] WHERE LEN(FINALADDR) < 10 AND LEN(ADDR_ML) > 15


select * from [Regaddress_20190219] where city_ml <> city_R --787

select lk,  [Address],  finaladdr from [Regaddress_20190219] order by lk --49499