Using SUBSTRING in the WHERE clause

I need to compare address's from two different tables.  I only want those rows that don't match.

HBM_Address HBMa
HBMa.Address = '123 Corner Lane'

Contacts c
c.AddrLines = '123 Corner Lane  Mission Viejo, CA 92666'
c.City = 'Mission Viejo'
c.State = 'CA'
c.Zip = '92666'

I (-5) to account for the spaces used to separate the various fields in a.Addrlines.  Here is my query so far.

SELECT top 100 HBMc.[CLIENT_NAME] , c.FullName, HBMc.address_uno,  a.addrlines, HBMa.Address1

FROM HBM_Client HBMc
JOIN CONTACTS c ON c.CONTACTS = HBMc._PROLAW_FK
JOIN ADDRESS a ON c.CONTACTS = a.Contacts
JOIN HBM_Address HBMa ON HBMa.Address_Uno = HBMc.Address_Uno
JOIN HBM_Name HBMn ON HBMn.Name_Uno = HBMc.Name_Uno
WHERE a.IsMailing = 'Y'
AND substring(ISNULL(a.addrlines, ' '), 1, datalength(ISNULL(a.addrlines, ' ')) - datalength(a.CITY)-datalength(a.STATE)-datalength(a.ZIP) - 5) <> HBMa.Address1

Open in new window


When I run this query I get the following error.

'Invalid length parameter passed to the LEFT or SUBSTRING function.'

How can I accomplish what I am trying to do?
huerita37Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
I would use SQL functions like EXCEPT for differences and INTERSECT for matching rows between 2 tables. https://msdn.microsoft.com/en-us/library/ms188055(v=sql.100).aspx
0
huerita37Author Commented:
I don't think I can use that because the fields are not identical.  I need to extract data out of one field to compare to another.

If I can use it, I need an example.  I looked at the article and it just a straight compare.
0
lcohanDatabase AnalystCommented:
As far as the original question most likely the issue is you're getting negative value at
...
datalength(a.CITY)-datalength(a.STATE)-datalength(a.ZIP)
...
so I tried to add the ABS but if that's still erroring out is probably another NULL vallue that needs and ISNULL function on City, State, or zip.

SELECT top 100 HBMc.[CLIENT_NAME] , c.FullName, HBMc.address_uno,  a.addrlines, HBMa.Address1
      FROM HBM_Client HBMc
            JOIN CONTACTS c ON c.CONTACTS = HBMc._PROLAW_FK
            JOIN [ADDRESS] a ON c.CONTACTS = a.Contacts
            JOIN HBM_Address HBMa ON HBMa.Address_Uno = HBMc.Address_Uno
            JOIN HBM_Name HBMn ON HBMn.Name_Uno = HBMc.Name_Uno
WHERE a.IsMailing = 'Y'
            AND substring(ISNULL(a.addrlines, ' '), 1, datalength(ISNULL(a.addrlines, ' ')) - ABS(datalength(a.CITY)-datalength(a.STATE)-datalength(a.ZIP)) - 5) <> HBMa.Address1
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

lcohanDatabase AnalystCommented:
To use a EXCEPT or INTERSECT I believe you could get a list of records - think ID's of the matching rows that you can use to join back to the other tables and to get the rest of the data:


SELECT substring(ISNULL(a.addrlines, ' '), 1, datalength(ISNULL(a.addrlines, ' ')) - datalength(a.CITY)-datalength(a.STATE)-datalength(a.ZIP) - 5) as Addres1 FROM CONTACTS WHERE IsMailing = 'Y'
EXCEPT
SELECT Address1 FROM HBMa
0
awking00Commented:
You could also use nested replace functions replacing the city, state and zip, then trim the spaces. On the other hand, why not just "where charindex(HBMa.Address1,a.addrlines) = 0"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
huerita37Author Commented:
This was such a mess but here is what I was able to use.
 (CHARINDEX(CHAR(13), A.addrlines, 5) > 0 AND SUBSTRING(REPLACE(REPLACE(CAST(a.addrlines AS nvarchar(2000)), CHAR(13), '\\n'), CHAR(10), ''), 1, CHARINDEX('\\n', REPLACE(REPLACE(CAST(a.addrlines AS nvarchar(2000)), CHAR(13), '\\n'), CHAR(10), '')) - 1)
        <> hbma.address1)

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.