troubleshooting Question

SQL format question

Avatar of huerita37
huerita37Flag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
9 Comments2 Solutions94 ViewsLast Modified:
I have a query that runs in my c# program.  I have a full address field in one DB that needs to be parsed and inputted into various fields in another DB.  Here is an example of the full address.

addrLines = '123 Technology Ste. 205 Santa Monica, CA 92675'
This needs to be cut up and inputted into the correct fields in another DB.  For example:

addLine1 = '123 Techology'
addLine2 = 'Ste. 205'
City = 'Santa Monica'
State = 'CA'
Zip = '92675'


When the below query is run inside the program here is my result.

addLine1 = '123 Techology'
addLine2 = 'te. 205'
City = 'Santa Monica'
State = 'CA'
Zip = '92675'

When I run the query using SQL Server Management Studio it works correctly.  When I run the code in the program it cuts off the 1st character in the addLine2 field only.  Every thing else works fine.

Any idea why this might happen?  Here is the query incase you need the info.

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)

Open in new window

ASKER CERTIFIED SOLUTION
Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 2 Answers and 9 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 2 Answers and 9 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004