June 14, 2011 at 2:08 pm
SUBSTRING(ADDR,1,(LEN(ADDR) - LEN(TRIM(REVERSE(SUBSTRING(REVERSE(ADDR),1,FINDSTRING(REVERSE(ADDR),' ',1)))))))
How i can convert this expression in t-sql syntax. Thanks in advance.
June 14, 2011 at 2:53 pm
SSIS FINDSTRING becomes CHARINDEX in T-SQL.
SSIS TRIM becomes LTRIM + RTRIM in T-SQL.
The rest have the same names between SSIS and T-SQL so you can look them up in BOL to verify the T-SQL syntax.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 15, 2011 at 4:47 pm
Thanks for reply, Here is my complete question
Here is my source
Table1
ID ADDR
1 21345 NORWAL BLVD
Table2
ID ADDR
7 21345 NORWAL
My question is I want to update my Table.ID and here is my only link ADDR. I am using this update syntax
UPDATE HG2
SET ID = HG1.ID
FROM HG2
INNER JOIN HG1
ON HG1.ADDR = HG2.SUBSTRING(ADDR,1,(LEN(ADDR) - LEN(LTRIM(RTRIM(REVERSE(SUBSTRING(REVERSE(ADDR),1,charindex(REVERSE(ADDR),' ',1))))))))
Basically I am trying to trim BLVD from TABL1 and try to link to Table2.ADDR and update Table2.ID 7 to Table.ID 1. Please let me where I am doing wrong. Thanks in advance.
June 15, 2011 at 7:59 pm
Hmm... In this 'simple' case you're trying to remove the 'what type of road' qualifier from the name of the address... correct? To Make sure you're not duplicating addresses.
If this is true, you might be heading down a blind alley. If you're attempting to process addresses, there's a host of techniques and several products available (one of which is DQXI from SAP) that can decode them.
If not, then I've found that a compounded 'key' starting with the ZIP Code (US addresses, obviously, +4 if available otherwise use '0000') followed by the numeric designation(s) of the address (there's folks in apartments and condos), then a suite/apartment/condo number then followed by the first named instance of the street/road can get a very good approximation of an address. Append the suite or apartment/condo value to the end but before the named instance of the road/street/etc.
In almost all cases (okay only about 990/1000), you'll find that the ZIP+4+numericaddress+suite/apartment/condo number+street name will result in unique addresses unless you're looking for a name. Salt Lake City, Utah is an exception to look into.
--SJTerrill--
June 15, 2011 at 9:57 pm
The question comes up on these forums regularly and and trying to match addresses using T-SQL language features is a losing proposition. Now that you have revealed more about what you're trying to do...I agree with SJTerrill on the point about adding address-standardization software as a component in your environment.
MelissaData is the one I usually plug when the topic comes up. They offer SSIS components and the Address Object which can be used from pretty much any programming language on pretty much any platform.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 15, 2011 at 10:32 pm
Guys thanks for reply, I know i can accomplish this one through SSIS without buy any tool, But here i am stuck because of tsql code, and i want to update one table to other. If someone can help me to correct my syntax it would be great and at least point my mistake where i am wrong, Thanks.
June 15, 2011 at 10:49 pm
Wow...you're still asking for rope 😛
If all your data follows your example data and all you're trying to do is account for a missing 'BLVD' here and there you can use something along these lines:
UPDATE HG2
SET ID = HG1.ID
FROM HG2
INNER JOIN HG1 ON HG1.ADDR LIKE HG2.ADDR + '%'
OR HG2.ADDR LIKE HG1.ADDR + '%'
If not then you have all the function names you'll need...Books Online and Google are your friends.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 16, 2011 at 7:34 pm
Here is the syntax in case some one need it,
Update HG2
set id = hg1.id
from hg2
inner join hg1
on hg2.addr = HG1.ADDR = SUBSTRING(ADDR,1,(LEN(HG2.ADDR) - LEN(LTRIM(RTRIM(REVERSE(SUBSTRING(REVERSE(HG2.ADDR),1,charindex(REVERSE(HG2.ADDR),' ',1))))))))
Its good idea if your company pay for third party tools like you guys suggest, but when your company not going to pay then you have to find the solution. Thanks all of you guys.
June 16, 2011 at 7:49 pm
Rocky,
Your syntax is not at issue. It's what you're trying to do with address data that may come from various and 'random' sources. There's no guarantee that Boulevard is spelled correctly (or any other 'street'-type name). Not to mention spelling of the 'street' name itself (Sepulveda vs. Sepulvida). There's also the common abbreviations of Boulevard to blvd. or blv. or bl. The same thing happens with Lane to Ln. or Road to Rd or Street to St. or Way to wy. Sometimes with or without the period.
If you wish to perform a JOIN on an address, I recommend _first_ uniquely identifying that address and assigning it an artificial key. As opc stated, this is best accomplished via commercial tools that explicitly reference official postal data. Be prepared to update your postal-derived data into your existing data. Such databases are available via subscription from USPS (as an example). They're relatively cheap, BTW... You will have to update your address data as things like ZIP codes change.
If you can't get a consistent, reliable feed from governmental postal data... well... I still recommend starting from the highest order address data (for the US, anyway): ZIP, then the ZIP + 4 code, then the apartment/suite number, then (and only then) the first portion of the address string that resembles a 'street' name. I might even use a 'sound-alike' function to store the data in that compound key before attempting a join.
It can, obviously, get messy.
--Sauron J. Terrill
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply