--从excel中导入数据存放表
CREATE TABLE [dbo].[AddressPartHistory](
[AddressPartID] [uniqueidentifier] NOT NULL,
[ZIPCode] [char](5) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[CityName] [nvarchar](80) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[AreaName] [nvarchar](80) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[StreetName] [nvarchar](80) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[More] [nvarchar](80) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
CONSTRAINT [PK_ADDRESSPARTHISTORY] PRIMARY KEY CLUSTERED
(
[AddressPartID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
--要转存的表
CREATE TABLE [dbo].[AddressPart](
[AddressPartID] [uniqueidentifier] NOT NULL,
[ZIPCode] [char](5) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[CityID] [char](4) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[CityName] [nvarchar](80) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[AreaID] [char](4) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[AreaName] [nvarchar](80) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[StreetID] [char](4) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[StreetName] [nvarchar](80) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[StreetConfine] [int] NULL,
[PartID] [char](4) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[PartNo] [int] NULL,
[PartConfine] [int] NULL,
[laneID] [char](4) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[lane1] [int] NULL,
[lane2] [int] NULL,
[laneConfine] [int] NULL,
[alleyID] [char](4) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[alley1] [int] NULL,
[alley2] [int] NULL,
[alleyConfine] [int] NULL,
[NoID] [char](4) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[No1] [int] NULL,
[No1c] [int] NULL,
[No2] [int] NULL,
[No2c] [int] NULL,
[NoConfine] [int] NULL,
[floorID] [char](4) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[floor1] [int] NULL,
[floor2] [int] NULL,
[floorConfine] [int] NULL,
[fullPart] [nvarchar](400) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
CONSTRAINT [PK_ADDRESSPART1] PRIMARY KEY CLUSTERED
(
[AddressPartID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
--表AddressPartHistory 中More中的示例数据,可能不太全。
--單351號以上
--單111號以下
--單565號至591號
--199巷雙30號以上
--87之1號
--雙200之4號以下
--雙16號至20之2號
--雙620號至1002巷
--雙188之6號至198號
--159巷單17號以下
--288號6樓以下
--187號2至3樓
--79之1至之10號4樓以上
--132巷連6弄至9弄
--912巷59弄單13號至15號
--160巷雙2弄至10號
--連256弄至399之13號
--存储过程
/*
功能: 將表AddressPartHistory中數據導入表AddressPart中
创建时间: 2008/7/22
创建者: Jam.Fu
*/
ALTER PROC Logt_IAddressPart
as
DELETE FROM AddressPart
INSERT INTO
AddressPart
select
AddressPartID,
ZIPCode,
(select top 1 CityID from EmpSYS.dbo.City where City=AddressPartHistory.CityName) as CityID,
CityName,
(select top 1 AreaID from EmpSYS.dbo.Area where Area=AddressPartHistory.AreaName) as AreaID,
AreaName,
CASE
WHEN right(streetName,1)='段'
THEN left(streetName,len(StreetName)-2)
ELSE streetName
END AS StreetName,
CASE
WHEN right(streetName,1)<>'段'
THEN
CASE
WHEN left(More,1)='全'
then 0
when left(More,1)='連'
then 1
when left(More,1)='單'
then 2
when left(More,1)='雙'
then 3
when left(More,1)='單全'
then 4
when left(More,1)='雙全'
then 5
else 0
end
ELSE null
END as StreetConfine,
NULL AS PartID,
CASE
WHEN right(streetName,1)='段'
THEN dbo.Lgtc_Fn_ToNum(left(right(streetname,2),1))
ELSE NULL
END AS PartNo,
CASE
WHEN right(streetName,1)='段'
THEN
CASE
WHEN left(More,1)='全'
then 0
when left(More,1)='連'
then 1
when left(More,1)='單'
then 2
when left(More,1)='雙'
then 3
when left(More,1)='單全'
then 4
when left(More,1)='雙全'
then 5
else 0
end
ELSE NULL
END as PartConfine,
NULL AS laneID,
Case
WHEN CHARINDEX('巷',More)>0
THEN
CASE
WHEN More LIKE '%巷以上%'
THEN 0
WHEN SUBSTRING(More,0,CHARINDEX('巷',More)) LIKE '%號至%'
THEN NULL
WHEN More LIKE '%巷至%'
AND SUBSTRING(SUBSTRING(More,CHARINDEX('巷至',More)+2,LEN(More)),PATINDEX('%[^0-9之]%',SUBSTRING(More,CHARINDEX('巷至',More)+2,LEN(More))),LEN(SUBSTRING(More,CHARINDEX('巷至',More)+2,LEN(More)))) LIKE '號%'
THEN NULL
WHEN SUBSTRING(More,0,CHARINDEX('巷',More)) NOT LIKE '%號至%'
THEN
CASE
WHEN PATINDEX('%[^0-9]%',REVERSE(LEFT(More,CHARINDEX('巷',More)-1)))=0 AND More NOT LIKE '%巷以上%'
THEN CONVERT(INT,
CASE WHEN ISNUMERIC(LEFT(More,CHARINDEX('巷',More)-1))=1
THEN LEFT(More,CHARINDEX('巷',More)-1)
ELSE NULL
END)
WHEN PATINDEX('%[^0-9]%',REVERSE(LEFT(More,CHARINDEX('巷',More)-1)))>0 AND More NOT LIKE '%巷以上%'
THEN CONVERT(INT,
CASE WHEN ISNUMERIC(REVERSE(LEFT(REVERSE(LEFT(More,CHARINDEX('巷',More)-1)),PATINDEX('%[^0-9]%',REVERSE(LEFT(More,CHARINDEX('巷',More)-1)))-1)))=1
THEN REVERSE(LEFT(REVERSE(LEFT(More,CHARINDEX('巷',More)-1)),PATINDEX('%[^0-9]%',REVERSE(LEFT(More,CHARINDEX('巷',More)-1)))-1))
ELSE NULL
END )
ELSE NULL
END
ELSE NULL
END
ELSE NULL
END AS lane1,
CASE
WHEN More like '%巷至%'
THEN CONVERT(INT,CASE
WHEN ISNUMERIC(LEFT(SUBSTRING(More,CHARINDEX('巷至',More)+2,LEN(More)-1),LEN(SUBSTRING(More,CHARINDEX('巷至',More)+2,LEN(More)-1))- PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,CHARINDEX('巷至',More)+2,LEN(More)-1)))))=1 --弄至与后面的一个弄或号之间为纯数字
AND SUBSTRING(More,CHARINDEX('巷至',More)+2,LEN(More)-1) LIKE '%巷%' --弄至后接着还有一个弄
THEN LEFT(SUBSTRING(More,CHARINDEX('巷至',More)+2,LEN(More)-1),LEN(SUBSTRING(More,CHARINDEX('巷至',More)+2,LEN(More)-1))- PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,CHARINDEX('巷至',More)+2,LEN(More)-1))))
-- WHEN ISNUMERIC(LEFT(SUBSTRING(More,CHARINDEX('巷至',More)+2,LEN(More)-1),LEN(SUBSTRING(More,CHARINDEX('巷至',More)+2,LEN(More)-1))- PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,CHARINDEX('巷至',More)+2,LEN(More)-1)))))=0
-- THEN SUBSTRING(LEFT(SUBSTRING(More,CHARINDEX('巷至',More)+2,LEN(More)-1),LEN(SUBSTRING(More,CHARINDEX('巷至',More)+2,LEN(More)-1))- PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,CHARINDEX('巷至',More)+2,LEN(More)-1)))),0, PATINDEX('%[^0-9]%',LEFT(SUBSTRING(More,CHARINDEX('巷至',More)+2,LEN(More)-1),LEN(SUBSTRING(More,CHARINDEX('巷至',More)+2,LEN(More)-1))- PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,CHARINDEX('巷至',More)+2,LEN(More)-1))))))
ELSE NULL
END
)
WHEN More LIKE '%巷以下%'
THEN 99999
WHEN More LIKE '%號至%'
AND
SUBSTRING(SUBSTRING(More,CHARINDEX('號至',More)+2,LEN(More)),PATINDEX('%[^0-9之]%',SUBSTRING(More,CHARINDEX('號至',More)+2,LEN(More))),LEN(SUBSTRING(More,CHARINDEX('號至',More)+2,LEN(More)))) LIKE '巷%'
THEN NULL
WHEN More NOT LIKE '%巷至%' AND More NOT LIKE '%巷以下%'
THEN CONVERT(INT,CASE WHEN
ISNUMERIC(REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('巷',More))),0,PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,0,CHARINDEX('巷',More)))))))=1
AND PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,0,CHARINDEX('巷',More))))<>0
THEN REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('巷',More))),0,PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,0,CHARINDEX('巷',More))))))
WHEN ISNUMERIC(REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('巷',More))),0,PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,0,CHARINDEX('巷',More)))))))=1
AND PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,0,CHARINDEX('巷',More))))=0
THEN REVERSE(SUBSTRING(More,0,CHARINDEX('巷',More)))
ELSE NULL
END)
ELSE NULL
END AS lane2,
CASE
WHEN More LIKE '%巷全%'
THEN 0
WHEN More LIKE '%巷連%'
THEN 1
WHEN More LIKE '%巷單%' AND More NOT LIKE '%巷單全%'
THEN 2
WHEN More LIKE '%巷雙%' AND More NOT LIKE '%巷雙全%'
THEN 3
WHEN More LIKE '%巷單全%'
THEN 4
WHEN More LIKE '%巷雙全%'
THEN 5
END as laneConfine,
null as alleyID,
Case
WHEN CHARINDEX('弄',More)>0
THEN
CASE
WHEN More LIKE '%弄以上%'
THEN 0
WHEN SUBSTRING(More,0,CHARINDEX('弄',More)) LIKE '%號至%'
THEN NULL
WHEN SUBSTRING(More,0,CHARINDEX('弄',More)) NOT LIKE '%號至%'
THEN
CASE
WHEN PATINDEX('%[^0-9]%',REVERSE(LEFT(More,CHARINDEX('弄',More)-1)))=0 AND More NOT LIKE '%弄以上%'
THEN CONVERT(INT,
CASE WHEN ISNUMERIC(LEFT(More,CHARINDEX('弄',More)-1))=1
THEN LEFT(More,CHARINDEX('弄',More)-1)
ELSE NULL
END)
WHEN PATINDEX('%[^0-9]%',REVERSE(LEFT(More,CHARINDEX('弄',More)-1)))>0 AND More NOT LIKE '%弄以上%'
THEN CONVERT(INT,
CASE WHEN ISNUMERIC(REVERSE(LEFT(REVERSE(LEFT(More,CHARINDEX('弄',More)-1)),PATINDEX('%[^0-9]%',REVERSE(LEFT(More,CHARINDEX('弄',More)-1)))-1)))=1
THEN REVERSE(LEFT(REVERSE(LEFT(More,CHARINDEX('弄',More)-1)),PATINDEX('%[^0-9]%',REVERSE(LEFT(More,CHARINDEX('弄',More)-1)))-1))
ELSE NULL
END )
ELSE NULL
END
ELSE NULL
END
ELSE NULL
END AS alley1,
CASE
WHEN More like '%弄至%'
THEN CONVERT(INT,CASE
WHEN ISNUMERIC(LEFT(SUBSTRING(More,CHARINDEX('弄至',More)+2,LEN(More)-1),LEN(SUBSTRING(More,CHARINDEX('弄至',More)+2,LEN(More)-1))- PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,CHARINDEX('弄至',More)+2,LEN(More)-1)))))=1 --弄至与后面的一个弄或号之间为纯数字
AND SUBSTRING(More,CHARINDEX('弄至',More)+2,LEN(More)-1) LIKE '%弄%' --弄至后接着还有一个弄
THEN LEFT(SUBSTRING(More,CHARINDEX('弄至',More)+2,LEN(More)-1),LEN(SUBSTRING(More,CHARINDEX('弄至',More)+2,LEN(More)-1))- PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,CHARINDEX('弄至',More)+2,LEN(More)-1))))
WHEN ISNUMERIC(LEFT(SUBSTRING(More,CHARINDEX('弄至',More)+2,LEN(More)-1),LEN(SUBSTRING(More,CHARINDEX('弄至',More)+2,LEN(More)-1))- PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,CHARINDEX('弄至',More)+2,LEN(More)-1)))))=0
THEN SUBSTRING(LEFT(SUBSTRING(More,CHARINDEX('弄至',More)+2,LEN(More)-1),LEN(SUBSTRING(More,CHARINDEX('弄至',More)+2,LEN(More)-1))- PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,CHARINDEX('弄至',More)+2,LEN(More)-1)))),0, PATINDEX('%[^0-9]%',LEFT(SUBSTRING(More,CHARINDEX('弄至',More)+2,LEN(More)-1),LEN(SUBSTRING(More,CHARINDEX('弄至',More)+2,LEN(More)-1))- PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,CHARINDEX('弄至',More)+2,LEN(More)-1))))))
ELSE NULL
END
)
WHEN More LIKE '%弄以下%'
THEN 99999
WHEN More NOT LIKE '%弄至%' AND More NOT LIKE '%弄以下%'
THEN CONVERT(INT,CASE WHEN
ISNUMERIC(REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('弄',More))),0,PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,0,CHARINDEX('弄',More)))))))=1
AND PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,0,CHARINDEX('弄',More))))<>0
THEN REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('弄',More))),0,PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,0,CHARINDEX('弄',More))))))
WHEN ISNUMERIC(REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('弄',More))),0,PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,0,CHARINDEX('弄',More)))))))=1
AND PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,0,CHARINDEX('弄',More))))=0
THEN REVERSE(SUBSTRING(More,0,CHARINDEX('弄',More)))
ELSE NULL
END)
ELSE NULL
END AS alley2,
CASE
WHEN More LIKE '%弄全%'
THEN 0
WHEN More LIKE '%弄連%'
THEN 1
WHEN More LIKE '%弄單%' AND More NOT LIKE '%弄單全%'
THEN 2
WHEN More LIKE '%弄雙%' AND More NOT LIKE '%弄雙全%'
THEN 3
WHEN More LIKE '%弄單全%'
THEN 4
WHEN More LIKE '%弄雙全%'
THEN 5
END as alleyConfine,
null as NoID,
--号1的处理
CASE
WHEN More LIKE '%巷至%'
AND
SUBSTRING(SUBSTRING(More,CHARINDEX('巷至',More)+2,LEN(More)),PATINDEX('%[^0-9之]%',SUBSTRING(More,CHARINDEX('巷至',More)+2,LEN(More))),LEN(SUBSTRING(More,CHARINDEX('巷至',More)+2,LEN(More)))) LIKE '號%'
THEN
CONVERT(INT,REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('巷',More))),0,PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,0,CHARINDEX('巷',More)))))))
WHEN More LIKE '%弄至%'
AND
SUBSTRING(SUBSTRING(More,CHARINDEX('弄至',More)+2,LEN(More)),PATINDEX('%[^0-9之]%',SUBSTRING(More,CHARINDEX('弄至',More)+2,LEN(More))),LEN(SUBSTRING(More,CHARINDEX('弄至',More)+2,LEN(More)))) LIKE '號%'
THEN
CONVERT(INT,REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('弄至',More))),0,PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,0,CHARINDEX('弄至',More)))))))
WHEN
More like '%至%'
AND
More NOT LIKE '%號至%'
AND
More NOT LIKE '%巷至%'
AND
More NOT LIKE '%弄至%'
and
More NOT LIKE '%鄰至%'
THEN
CASE
WHEN
ISNUMERIC(SUBSTRING(More,0,CHARINDEX('至',More))) =0
THEN
CASE
WHEN
PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('至',More))))=0
THEN
SUBSTRING(SUBSTRING(More,0,CHARINDEX('至',More)),0,PATINDEX('%[^0-9]%',SUBSTRING(More,0,CHARINDEX('至',More))))
WHEN
PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('至',More))))<>0
THEN
SUBSTRING(REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('至',More))),0,PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('至',More)))))),0,PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('至',More))),0,PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('至',More))))))))
ELSE NULL
END
WHEN
ISNUMERIC(SUBSTRING(More,0,CHARINDEX('至',More))) =1
THEN
CONVERT(INT,SUBSTRING(More,0,CHARINDEX('至',More)))
ELSE NULL
END
WHEN
More LIKE '%號%' And
Substring(Substring(More,0,Charindex('號',More)),0,Len(Substring(More,0,Charindex('號',More)))+2-Patindex('%[^0-9]%',Reverse(Substring(More,0,Charindex('號',More))))) NOT LIKE '%巷至%'--截取所有类似 “100巷至301号”的数据,去掉了多少号及后面部分
-- AND Substring(Substring(More,0,Charindex('號',More)),0,Len(Substring(More,0,Charindex('號',More)))+2-Patindex('%[^0-9]%',Reverse(Substring(More,0,Charindex('號',More))))) NOT LIKE '%弄至%'--截取所有类似 “100弄至301号”的数据,去掉了多少号及后面部分
-- AND SUBSTRING(SUBSTRING(More,CHARINDEX('號至',More)+2,LEN(More)),PATINDEX('%[^0-9之]%',SUBSTRING(More,CHARINDEX('號至',More)+2,LEN(More))),LEN(More)) LIKE '%號%' --排除掉號至多少巷的數據
THEN CASE
WHEN More LIKE '%號以上%'
THEN CASE
WHEN ISNUMERIC(REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),0,
PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More)))))))=0
THEN CONVERT(INT,SUBSTRING(Reverse(Substring(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),0,
PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More)))))),0,PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),0,
PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More)))))))))
WHEN ISNUMERIC(REVERSE(Substring(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),0,
PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More)))))))=1
THEN 0
ELSE NULL
END
WHEN More NOT LIKE '%號以上%'
THEN CASE
WHEN ISNUMERIC(SUBSTRING(More,0,CHARINDEX('號',More)))=1
THEN CONVERT(INT,SUBSTRING(More,0,CHARINDEX('號',More)))
WHEN
ISNUMERIC(REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),0,
PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More)))))))=1
THEN
CONVERT(INT,REVERSE(Substring(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),0,
PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More)))))))
WHEN
ISNUMERIC(REVERSE(Substring(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),0,
PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More)))))))=0
THEN
CASE
WHEN PATINDEX('%[^0-9]%',Reverse(Substring(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),0,
PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More)))))))<>0
THEN
CONVERT(INT,SUBSTRING(Reverse(Substring(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),0,
PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More)))))),0,PATINDEX('%[^0-9]%',Reverse(Substring(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),0,
PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More)))))))))
WHEN
PATINDEX('%[^0-9]%',Reverse(Substring(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),0,
PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More)))))))=0
THEN
CONVERT(INT,
SUBSTRING(SUBSTRING(More,0,CHARINDEX('號',More)),0,PATINDEX('%[^0-9]%',SUBSTRING(More,0,CHARINDEX('號',More)))))
ELSE NULL
END
ELSE NULL
END
ELSE NULL
END
ELSE NULL
END as No1,
CASE
WHEN
More LIKE '%號%'
THEN
CASE
WHEN
More LIKE '%號至%'
AND
ISNUMERIC(SUBSTRING(More,0,CHARINDEX('號',More)))=0
THEN
CASE
WHEN
PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))))=0
THEN
CONVERT(INT,SUBSTRING(SUBSTRING(More,0,CHARINDEX('號',More)),0,PATINDEX('%[^0-9]%',SUBSTRING(More,0,CHARINDEX('號',More)))))
WHEN
PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))))<>0
AND
ISNUMERIC(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),0,PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))))))=0
THEN
CONVERT(INT,REVERSE(SUBSTRING(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),0,PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))))),0,PATINDEX('%[^0-9]%',SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),0,PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More)))))))))
ELSE NULL
END
WHEN
More like '%至%'
AND
More NOT LIKE '%號至%'
AND
More NOT LIKE '%巷至%'
AND
More NOT LIKE '%弄至%'
and
More NOT LIKE '%鄰至%'
THEN
CASE
WHEN
PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('至',More))))=0
THEN
CONVERT(INT,REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('至',More))),0,PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,0,CHARINDEX('至',More)))))))
WHEN
PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('至',More))))<>0
AND
ISNUMERIC(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('至',More))),0,PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('至',More))))))=0
THEN
CONVERT(INT,REVERSE(SUBSTRING(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('至',More))),0,PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('至',More))))),0,PATINDEX('%[^0-9]%',SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('至',More))),0,PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('至',More)))))))))
END
ELSE NULL
END
ELSE NULL
END AS NO1c,
CASE
WHEN More LIKE '%巷至%'
AND
SUBSTRING(SUBSTRING(More,CHARINDEX('巷至',More)+2,LEN(More)),PATINDEX('%[^0-9之]%',SUBSTRING(More,CHARINDEX('巷至',More)+2,LEN(More))),LEN(SUBSTRING(More,CHARINDEX('巷至',More)+2,LEN(More)))) LIKE '號%'
THEN
CONVERT(INT,CASE
WHEN
ISNUMERIC(REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),0,PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More)))))))=1
THEN
REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),0,PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))))))
WHEN
ISNUMERIC(REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),0,PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More)))))))=0
THEN
SUBSTRING(REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),0,PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More)))))),0,PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),0,PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))))))))
END
)
WHEN More LIKE '%弄至%'
AND
SUBSTRING(SUBSTRING(More,CHARINDEX('弄至',More)+2,LEN(More)),PATINDEX('%[^0-9之]%',SUBSTRING(More,CHARINDEX('弄至',More)+2,LEN(More))),LEN(SUBSTRING(More,CHARINDEX('弄至',More)+2,LEN(More)))) LIKE '號%'
THEN
CONVERT(INT,CASE
WHEN
ISNUMERIC(REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),0,PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More)))))))=1
THEN
REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),0,PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))))))
WHEN
ISNUMERIC(REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),0,PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More)))))))=0
THEN
SUBSTRING(REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),0,PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More)))))),0,PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),0,PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))))))))
END
)
WHEN
More like '%至%'
AND
SUBSTRING(More,CHARINDEX('至',More)+1,LEN(More)) LIKE '%號%'
THEN
CONVERT(INT,CASE
WHEN
ISNUMERIC(SUBSTRING(SUBSTRING(More,CHARINDEX('至',More)+1,LEN(More)),0,PATINDEX('%[^0-9之]%',SUBSTRING(More,CHARINDEX('至',More)+1,LEN(More)))))=1
THEN
SUBSTRING(SUBSTRING(More,CHARINDEX('至',More)+1,LEN(More)),0,PATINDEX('%[^0-9之]%',SUBSTRING(More,CHARINDEX('至',More)+1,LEN(More))))
ELSE NULL
END
)
WHEN
More LIKE '%號%'
THEN
CASE
WHEN
More LIKE '%號以下%'
THEN
99999
WHEN
More LIKE '%號以上%'
THEN
CASE
WHEN
ISNUMERIC(REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號以上',More))),0,PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號以上',More)))))))=1
THEN
CONVERT(INT,REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號以上',More))),0,PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號以上',More)))))))
WHEN
ISNUMERIC(REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號以上',More))),0,PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號以上',More)))))))=0
THEN
CASE
WHEN
PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號以上',More))),0,PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號以上',More)))))))>0
THEN
CONVERT(INT,SUBSTRING(REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號以上',More))),0,PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號以上',More)))))),0,PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號以上',More))),0,PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號以上',More)))))))))
WHEN
PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號以上',More))),0,PATINDEX('%[^0-9之]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號以上',More)))))))=0
THEN
CONVERT(INT,SUBSTRING(More,0,PATINDEX('%[^0-9]%',More)))
ELSE NULL
END
ELSE NULL
END
WHEN
PATINDEX('%[^0-9之]%',SUBSTRING(More,0,CHARINDEX('號',More)))=0
THEN
CASE
WHEN
ISNUMERIC(SUBSTRING(More,0,CHARINDEX('號',More)))=1
THEN
CONVERT(INT,SUBSTRING(More,0,CHARINDEX('號',More)))
WHEN
ISNUMERIC(SUBSTRING(More,0,CHARINDEX('號',More)))=0
THEN
CONVERT(INT,SUBSTRING(SUBSTRING(More,0,CHARINDEX('號',More)),0,PATINDEX('%[^0-9]%',SUBSTRING(More,0,CHARINDEX('號',More)))))
ELSE NULL
END
WHEN
More like '%號至%'
THEN
CONVERT(INT,SUBSTRING(SUBSTRING(More,CHARINDEX('號至',More)+2,LEN(More)),0,PATINDEX('%[^0-9]%',SUBSTRING(More,CHARINDEX('號至',More)+2,LEN(More)))))
END
ELSE NULL
END AS No2,
CASE
WHEN
More like '%號至%'
THEN
CONVERT(INT,CASE
WHEN
ISNUMERIC(SUBSTRING(SUBSTRING(More,CHARINDEX('號至',More)+2,LEN(More)),0,PATINDEX('%[^0-9之]%',SUBSTRING(More,CHARINDEX('號至',More)+2,LEN(More)))))=0
THEN
REVERSE(SUBSTRING(REVERSE(SUBSTRING(SUBSTRING(More,CHARINDEX('號至',More)+2,LEN(More)),0,PATINDEX('%[^0-9之]%',SUBSTRING(More,CHARINDEX('號至',More)+2,LEN(More))))),0,PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(SUBSTRING(More,CHARINDEX('號至',More)+2,LEN(More)),0,PATINDEX('%[^0-9之]%',SUBSTRING(More,CHARINDEX('號至',More)+2,LEN(More))))))))
ELSE NULL
END
)
WHEN
More like '%至%'
AND
SUBSTRING(More,CHARINDEX('至',More)+1,LEN(More)) LIKE '%號%'
THEN
CONVERT(INT,CASE
WHEN
ISNUMERIC(SUBSTRING(SUBSTRING(More,CHARINDEX('至',More)+1,LEN(More)),0,PATINDEX('%[^0-9之]%',SUBSTRING(More,CHARINDEX('至',More)+1,LEN(More)))))=0
THEN
REVERSE(SUBSTRING(REVERSE(SUBSTRING(SUBSTRING(More,CHARINDEX('至',More)+1,LEN(More)),0,PATINDEX('%[^0-9之]%',SUBSTRING(More,CHARINDEX('至',More)+1,LEN(More))))),0,PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(SUBSTRING(More,CHARINDEX('至',More)+1,LEN(More)),0,PATINDEX('%[^0-9之]%',SUBSTRING(More,CHARINDEX('至',More)+1,LEN(More))))))))
ELSE NULL
END
)
ELSE NULL
END AS NO2c,
Case
WHEN
LEN(More)>CHARINDEX('號',More)
THEN
case
when
SUBSTRING(More,CHARINDEX('號',More)+1,LEN(More)) NOT LIKE '%巷%'
AND
SUBSTRING(More,CHARINDEX('號',More)+1,LEN(More)) NOT LIKE '%弄%'
AND
SUBSTRING(More,CHARINDEX('號',More)+1,LEN(More)) NOT LIKE '%斷%'
then
CASE
WHEN
SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More)))),LEN(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))))) LIKE '%全%'
AND
SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More)))),LEN(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))))) NOT LIKE '%全單%'
AND
SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More)))),LEN(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))))) NOT LIKE '%全雙%'
THEN
0
WHEN
SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More)))),LEN(REVERSE(CHARINDEX(More,0,CHARINDEX('號',More))))) LIKE '%連%'
THEN
1
WHEN
SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More)))),LEN(REVERSE(CHARINDEX(More,0,CHARINDEX('號',More))))) LIKE '%單%'
THEN
2
WHEN
SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More)))),LEN(REVERSE(CHARINDEX(More,0,CHARINDEX('號',More))))) LIKE '%雙%'
THEN
3
WHEN
SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More)))),LEN(REVERSE(CHARINDEX(More,0,CHARINDEX('號',More))))) LIKE '%全單%'
THEN
4
WHEN
SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('號',More))),PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(More,0,CHARINDEX('號',More)))),LEN(REVERSE(CHARINDEX(More,0,CHARINDEX('號',More))))) LIKE '%全雙%'
THEN
5
ELSE NULL
END
ELSE NULL
END
ELSE NULL
END AS NoConfine,
/*当前数据拆分,1楼,2至3楼,11楼以上或以下*/
null as floorID,
Case
WHEN
More LIKE '%樓%'
THEN
CASE
WHEN
More LIKE '%樓以下%'
THEN
-99
WHEN
More NOT LIKE '%樓以下%'
THEN
CASE
WHEN
ISNUMERIC(REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('樓',More))),0,PATINDEX('%[^0-9至]%',REVERSE(SUBSTRING(More,0,CHARINDEX('樓',More)))))))=1
THEN
CONVERT(INT,REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('樓',More))),0,PATINDEX('%[^0-9至]%',REVERSE(SUBSTRING(More,0,CHARINDEX('樓',More)))))))
WHEN
ISNUMERIC(REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('樓',More))),0,PATINDEX('%[^0-9至]%',REVERSE(SUBSTRING(More,0,CHARINDEX('樓',More)))))))=0
THEN
CONVERT(INT,SUBSTRING(REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('樓',More))),0,PATINDEX('%[^0-9至]%',REVERSE(SUBSTRING(More,0,CHARINDEX('樓',More)))))),0,PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('樓',More))),0,PATINDEX('%[^0-9至]%',REVERSE(SUBSTRING(More,0,CHARINDEX('樓',More)))))))))
ELSE NULL
END
ELSE NULL
END
ELSE NULL
END AS floor1,
CASE
WHEN
More LIKE '%樓%'
THEN
CASE
WHEN
More LIKE '%樓以上%'
THEN
99999
WHEN
More NOT LIKE '%樓以上%'
THEN
CASE
WHEN
ISNUMERIC(REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('樓',More))),0,PATINDEX('%[^0-9至]%',REVERSE(SUBSTRING(More,0,CHARINDEX('樓',More)))))))=1
THEN
CONVERT(INT,REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('樓',More))),0,PATINDEX('%[^0-9至]%',REVERSE(SUBSTRING(More,0,CHARINDEX('樓',More)))))))
WHEN
ISNUMERIC(REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('樓',More))),0,PATINDEX('%[^0-9至]%',REVERSE(SUBSTRING(More,0,CHARINDEX('樓',More)))))))=0
THEN
CONVERT(INT,SUBSTRING(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('樓',More))),0,PATINDEX('%[^0-9至]%',REVERSE(SUBSTRING(More,0,CHARINDEX('樓',More))))),0,PATINDEX('%[^0-9]%',SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('樓',More))),0,PATINDEX('%[^0-9至]%',REVERSE(SUBSTRING(More,0,CHARINDEX('樓',More))))))))
--REVERSE(SUBSTRING(REVERSE(SUBSTRING(More,0,CHARINDEX('樓',More))),0,PATINDEX('%[^0-9至]%',REVERSE(SUBSTRING(More,0,CHARINDEX('樓',More))))))
ELSE NULL
END
ELSE NULL
END
ELSE NULL
END AS floor2,
CASE
WHEN More LIKE '%樓全%'
THEN 0
WHEN More LIKE '%樓連%'
THEN 1
WHEN More LIKE '%樓單%' AND More NOT LIKE '%樓單全%'
THEN 2
WHEN More LIKE '%樓雙%' AND More NOT LIKE '%樓雙全%'
THEN 3
WHEN More LIKE '%樓單全%'
THEN 4
WHEN More LIKE '%樓雙全%'
THEN 5
END as floorConfine,
More from AddressPartHistory
--where
-- More like '%至%'
-- AND
-- More NOT LIKE '%號至%'
-- AND
-- More NOT LIKE '%巷至%'
-- AND
-- More NOT LIKE '%弄至%'
-- and
-- More NOT LIKE '%鄰至%'
-- and
-- More Like '%鄰%'
--where
--More like '%至%'
-- AND
-- More NOT LIKE '%號至%'
-- AND
-- SUBSTRING(More,CHARINDEX('至',More)+1,LEN(More)) LIKE '%號%'
--where
--More like '%之%號%'
--where
--CHARINDEX('巷',More)>0 and
--SUBSTRING(More,0,CHARINDEX('巷',More)) LIKE '%號至%'
--or
--(
-- CHARINDEX('巷至',More)>0 and
-- SUBSTRING(More,CHARINDEX('巷至',More),Len(More)) LIKE '%號%'
--)