Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jjz
Starting Member
31 Posts |
 Posted - 2010-04-20 : 06:30:06
|
| HiMay someone please assist.I am trying to compare two columns both as shown below:One table has :Compaq Deskpro 2000 PCAnd the other has:COMPAQ DESKPRO 2000 5133 M1080my table has over 1000 records and I therefore cannot go through each record one by one.is there an easy way that i can use to get do this comparison.The like is not working.Thank you |
|
|
jjz
Starting Member
31 Posts |
Posted - 2010-04-20 : 06:49:28
|
| Additional info.I am working on SQL2005The like works when there is an identical match between the two columns and the rest it doesnt seem to be able to compare it.I obviously cannot use wilcards i.e. % when comparing columns |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-04-20 : 06:51:50
|
| select * from mytable ainner join mytable b on substr(upper(a.mycol),1,10) = substr(upper(b.mycol1),1,10)where upper(a.mycol) <> upper(b.mycol)Something like the above may get you close. it'll perform a comparison on the 1st 10 digits of the field mycol. adjust the substr bit to tune the success rate for your data. This will be a hit and miss affair - I suspect you won't be able to do it 100% with some eye-balling. |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2010-04-20 : 07:12:50
|
| UPPER() is not needed, unless the database is in Case-sensitive collation. Another option to check for is CONTAINS predicate in full-text search.Harsh Athalyehttp://www.letsgeek.net/ |
|
|
|
jjz
Starting Member
31 Posts |
Posted - 2010-04-20 : 07:29:01
|
| you guys are my rock star, thanx a mil.you are right Andrew, i am not getting it 100% but at least i now get better results.Once again thanx guys. |
|
|
|
|
|
|
Comparing the content of 2 columns using like