Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
SQL format question
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.
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)
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Yes but that isn't what is wrong.
I tried an experiment. Instead of
I used the address1 and it works fine.
What is wrong when I parse out my PLAddress2? Again, it works when I run the query in SQL Server Management.
I tried an experiment. Instead of
HBMa.address2 = CAST(cteAddress1.PLAddress2 AS VARCHAR(60))
I used the address1 and it works fine.
HBMa.address2 = CAST(cteAddress1.PLAddress1 AS VARCHAR(60))
What is wrong when I parse out my PLAddress2? Again, it works when I run the query in SQL Server Management.
OK. Thanks for answering this question. I think I know where the problem is.
have a look at your ctePos. There you get the positions of the delimiters and use the contacts as the key. If a contact has a few addresses then it is possible that the positions will be used for the wrong addresses in your next cteAddress1 where you us "JOIN ctePos ON ctePos.Contacts = cteAddress.Contacts".
To fix this problem I suggest you enhance the query like this. Bring the address from the first cte to the second and to the next and get rid of the JOIN in the second cte:
have a look at your ctePos. There you get the positions of the delimiters and use the contacts as the key. If a contact has a few addresses then it is possible that the positions will be used for the wrong addresses in your next cteAddress1 where you us "JOIN ctePos ON ctePos.Contacts = cteAddress.Contacts".
To fix this problem I suggest you enhance the query like this. Bring the address from the first cte to the second and to the next and get rid of the JOIN in the second cte:
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,
FixedAddress
FROM cteAddress ),
cteAddress1 AS (
SELECT ctePos.FixedAddress, ctePos.Contacts,
CASE WHEN ctePos.FixedAddress <> '' AND nPos1 = 0
THEN ctePos.FixedAddress
WHEN ctePos.FixedAddress <> '' AND nPos1 > 0
THEN CAST(SUBSTRING(ctePos.FixedAddress, 1, nPos1 -1) AS varchar(60)) ELSE '' END as PLAddress1,
CASE WHEN ctePos.FixedAddress <> '' AND nPos2 > nPos1 AND nLength > 0
THEN CAST(LTRIM(SUBSTRING(ctePos.FixedAddress, nPos1 + 3, nLength)) AS varchar(60)) ELSE '' END AS PLAddress2
FROM ctePos)
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)
Chaau -
I tried that and it gave me the same results. It is cutting off the first character for the Address2 line.
I tried that and it gave me the same results. It is cutting off the first character for the Address2 line.
I found the problem.
I knew it had to do with how I was getting Address2 out of the full address. The reason I know this is because when I set
addLine2 = Address1
No characters were ommitted. I looked at this line:
I was replacing CHAR(10) with ''. I decided to try to replace it with ' '. This seems to work and I am getting the correct data.
I am still baffled on why it would work the way I wanted it to in SQL Server but not in my program.
Any opinions?
I knew it had to do with how I was getting Address2 out of the full address. The reason I know this is because when I set
addLine2 = Address1
No characters were ommitted. I looked at this line:
SELECT a.Contacts, REPLACE(REPLACE(CAST(a.addrlines AS nvarchar(2000)), CHAR(13), '\\n'), CHAR(10), ' ') AS FixedAddress
I was replacing CHAR(10) with ''. I decided to try to replace it with ' '. This seems to work and I am getting the correct data.
I am still baffled on why it would work the way I wanted it to in SQL Server but not in my program.
Any opinions?
I was replacing CHAR(10) with ''. I decided to try to replace it with ' '. This seems to work and I am getting the correct data.SSMS treats different the CHAR(10) and CHAR(13) from others applications.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial