NOT LIKE -comparing 2 columns - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

NOT LIKE -comparing 2 columns

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-08-15 : 02:00:26

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

545 Posts

Posted - 2013-08-15 : 02:48:48

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

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-08-15 : 02:53:17

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 '

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-08-15 : 03:19:22

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

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-08-15 : 04:39:53

Thanks. Works :-)

sivadss2007
Starting Member

18 Posts

Posted - 2013-08-28 : 08:25:32

You just increase the size of your variable

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

P.Siva

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources