Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 NOT LIKE -comparing 2 columns
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

collie
Constraint Violating Yak Guru

400 Posts

Posted - 08/15/2013 :  02:00:26  Show Profile  Reply with Quote
Hi,

I want to compare 2 columns from 2 different tables and return all rows in table 1 where value isn't like in table 2. They don't have to be the exact match.

Why do the following statements get returned('values are diff'
) ?

declare @mycolumn1 nvarchar(28) 
declare @mycolumn2 nvarchar(28) 

set @mycolumn1='mickey mouse donald duck walt disney'
set @mycolumn2=' mickey mouse donald duck walt disney'

if  LTRIM(Rtrim(@mycolumn2))  NOT LIKE  LTRIM(Rtrim( @mycolumn1 ))    

select 'values are diff'
 
else   
select 'values are same'


Thanks

stepson
Aged Yak Warrior

Romania
545 Posts

Posted - 08/15/2013 :  02:48:48  Show Profile  Reply with Quote
declare your variables a little bigger

declare @mycolumn1 nvarchar(128) 
declare @mycolumn2 nvarchar(128) 



S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

collie
Constraint Violating Yak Guru

400 Posts

Posted - 08/15/2013 :  02:53:17  Show Profile  Reply with Quote
Thanks. I did as you suggested and it did work for the example i gave but not in the following case:

set @mycolumn1='mickey mouse donald duck walt disney'
set @mycolumn2=' mickey mouse donald duck walt '
Go to Top of Page

stepson
Aged Yak Warrior

Romania
545 Posts

Posted - 08/15/2013 :  03:19:22  Show Profile  Reply with Quote
use % something like this:


if not (LTRIM(Rtrim( @mycolumn1 )) like '%'+LTRIM(Rtrim(@mycolumn2))+'%')



S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

collie
Constraint Violating Yak Guru

400 Posts

Posted - 08/15/2013 :  04:39:53  Show Profile  Reply with Quote
Thanks. Works :-)
Go to Top of Page

sivadss2007
Starting Member

India
18 Posts

Posted - 08/28/2013 :  08:25:32  Show Profile  Reply with Quote
You just increase the size of your variable

declare @mycolumn1 nvarchar(200)
declare @mycolumn2 nvarchar(200)

P.Siva
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000