June 25, 2019 at 4:00 pm
I have two databases that are replication from Home office to location and Location to Home office. (in house built process)
I have been tasked to compare the HO and Loc's data. I have some varchar fields that when looking at the data in a query they appear to match However when i do a comparison HO.Desc = Loc.Desc i get false. I have found that there are returns and some times hidden spaces in one or the other value. I have done Rtrim(ltrim(loc.desc) = rtrim(ltrim(ho.desc) still the same results. Can some one give me some advice on how to do a comparison?
June 25, 2019 at 5:49 pm
You're asking for exact matches, which means exact matches. Are you tolerating matches if there are no spaces? If so
replace(HOC.Desc, ' ', '') = replace(Lo.Desc, ' ', '')
might work well.
June 25, 2019 at 5:56 pm
The spacing is the same with in the text strings, what i am finding is that when the user entered the date the may have put a tab, at the end or a return and is was saved to the database with the special chars, but for some strange reason when it replicated the special chars did not go but did stay in the original location. (I don't know the code behind for the replication i wonder if they striped it during replication?)
June 25, 2019 at 6:07 pm
You can account for those items by using multiple REPLACE() statements for tabs, CRLF, etc.
June 26, 2019 at 10:49 am
This is what i came up with, i think i got all the offending items
replace(replace(replace(replace(replace(Rtrim(loc.desc),char(13),' '),char(10),' '),char(44),' '),char(34),''),char(45),'') =
replace(replace(replace(replace(replace(Rtrim(HO.desc),char(13),' '),char(10),' '),char(44),' '),char(34),''),char(45),'')
June 26, 2019 at 4:46 pm
If you have embedded characters on one side - comparing will not be equal. You need to remove those characters for the match to be valid.
I would think you would want to know when that condition occurs because that is a difference in the values and should be noted. But, if you want to ignore those differences:
replace(HO.Desc, char(13), '') = replace(Loc.Desc, char(13), '')
If there are additional characters (e.g. line feed):
replace(replace(HO.Desc, char(13), ''), char(10), '') = replace(replace(Loc.Desc, char(13), ''), char(10), '')
Or - you can implement a CLR regex and use a regex expression for the comparison.
But - as I stated previously you really should call these out as actual differences since the goal is to compare the data and the data is actually different.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 26, 2019 at 4:50 pm
i am doing a replace for char(13),' '),char(10),' '),char(44),' '),char(34),''),char(45),'') on both sides, working great.
Always get some help or pointed in the right direction here.
June 26, 2019 at 5:01 pm
If you have a large volume of data to compare and your chosen solution is very slow, please post back.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 26, 2019 at 5:19 pm
one of my sets of tables to compare has over a million records, it is taking about 5-10 seconds so works fine.
Thanks for the help
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply