WITH cteAddress AS (SELECT a.Contacts, REPLACE(REPLACE(CAST(a.addrlines AS nvarchar(2000)), CHAR(13), '\\n'), CHAR(10), '') AS FixedAddress
FROM ADDRESS a
JOIN Contacts c ON a.CONTACTS = c.CONTACTS WHERE a.IsMailing = 'Y' AND a.addrlines IS NOT NULL ),
ctePos AS (
SELECT cteAddress.Contacts, CHARINDEX('\\n', cteAddress.FixedAddress) AS nPos1, CHARINDEX('\\n', cteAddress.FixedAddress, CHARINDEX('\\n', cteAddress.FixedAddress) + 1) AS nPos2,
CASE WHEN CHARINDEX('\\n', cteAddress.FixedAddress, CHARINDEX('\\n', cteAddress.FixedAddress) + 1) > 0
THEN CHARINDEX('\\n', cteAddress.FixedAddress, CHARINDEX('\\n', cteAddress.FixedAddress) + 1) - (CHARINDEX('\\n', cteAddress.FixedAddress) + 3) ELSE 0 END AS nLength
FROM cteAddress ),
cteAddress1 AS (
SELECT cteAddress.FixedAddress, cteAddress.Contacts,
CASE WHEN cteAddress.FixedAddress <> '' AND nPos1 = 0
THEN cteAddress.FixedAddress
WHEN cteAddress.FixedAddress <> '' AND nPos1 > 0
THEN CAST(SUBSTRING(cteAddress.FixedAddress, 1, nPos1 -1) AS varchar(60)) ELSE '' END as PLAddress1,
CASE WHEN cteAddress.FixedAddress <> '' AND nPos2 > nPos1 AND nLength > 0
THEN CAST(LTRIM(SUBSTRING(cteAddress.FixedAddress, nPos1 + 3, nLength)) AS varchar(60)) ELSE '' END AS PLAddress2
FROM cteAddress JOIN ctePos ON ctePos.Contacts = cteAddress.Contacts)
UPDATE HBMa SET HBMa.address1 = CAST(cteAddress1.PLAddress1 AS VARCHAR(60)), HBMa.address2 = CAST(cteAddress1.PLAddress2 AS VARCHAR(60)), HBMa.City = a.City,
HBMa.State_Code = (SELECT CASE WHEN a.State IS NOT NULL Then Abbr ELSE '' END
FROM tbl_ADRStates WHERE Abbr = a.State OR State = a.State),
HBMa.Post_Code = a.Zip, HBMa.COUNTRY_CODE = (SELECT ADRT FROM tbl_ADRCountry WHERE COUNTRY = tbl_ADRCountry.ProLaw)
FROM cteAddress
JOIN cteAddress1 ON cteAddress.Contacts = cteAddress1.Contacts
JOIN Contacts c ON cteAddress.CONTACTS = c.CONTACTS
JOIN [AESQL].[ADR_LIVE].[dbo].HBM_Client HBMc ON c.Contacts = HBMc._PROLAW_FK
JOIN ADDRESS a ON c.Contacts = a.Contacts
JOIN [AESQL].[ADR_LIVE].[dbo].HBM_Address HBMa ON HBMa.Address_Uno = HBMc.Address_Uno
JOIN [AESQL].[ADR_LIVE].[dbo].HBM_Name HBMn ON HBMn.Name_Uno = HBMc.Name_Uno
WHERE a.IsMailing = 'Y' AND (cteAddress.FixedAddress IS NOT NULL AND CHARINDEX('\\n', cteAddress.FixedAddress) > 0 AND cteAddress1.PLAddress1 <> hbma.address1
OR CAST(cteAddress1.PLAddress2 AS VARCHAR(60)) <> hbma.address2 OR a.CITY <> HBMa.City
OR (SELECT CASE WHEN a.State IS NOT NULL Then Abbr ELSE '' END
FROM tbl_ADRStates WHERE Abbr = a.State OR State = a.State) <> HBMa.State_Code
OR a.ZIP <> HBMa.Post_Code)
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE