March 28, 2012 at 12:18 am
Hi,
I want to compare 2 columns streetname before cleansing and after cleansing and find the difference between the two.
the columns are as follows
AFTER
3803 STATE ROUTE 61
2041 PINE ST
10 MILTON ROAD
BEFORE
3803 STATE ROUTE
2041 /2 PINE ST
10 MILTON ROAD 12
DIFFERENCE
61
/2
12
Is there any way I can find the difference for string compare ?
I tried this but is solves half of the problem
COLDIFF = CASE WHEN LEN(RAW_StreetName) > LEN (AG_StreetName) THEN REPLACE(RAW_StreetName,AG_StreetName,'')
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
March 28, 2012 at 12:19 am
WHy not direct compariosn of colA <> colB ?
March 28, 2012 at 12:29 am
That is understood I want the exact difference as well.
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
March 28, 2012 at 2:27 am
I'll do it by doing a character by character comparision in a loop. Also keeping in mind the space we have before and after each word.....
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
March 28, 2012 at 3:24 am
No, don't use a loop. Here's something to get you started, although it's not the full solution. Make sure you understand the code - have a read of Jeff Moden's articles(s) on tally tables if you're struggling.
-- Test table
create table #Compare (Before varchar(30), After varchar(30))
-- Test data
INSERT INTO #Compare VALUES
('3803 STATE ROUTE 61', '3803 STATE ROUTE'),
('2041 PINE ST', '2041 /2 PINE ST'),
('10 MILTON ROAD', '10 MILTON ROAD 12')
.. and the rest of it is attached since something in the firewall here is stopping me from posting it inline.
John
March 28, 2012 at 4:40 am
Thanks for pointnig to Tally tables. That was something new for me to learn.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply