Difference between NOT LIKE and

• MattieNH

  SSCertifiable

  Points: 7463

  More actions

  March 23, 2006 at 7:25 am

  #168309

  I'm comparing an original and modified character string, and because they come from a text field, I'm using NOT LIKE instead of <>.    I've encountered a difference in the results, so I have two questions.  1)Why does LIKE consider [ different, and 2)what do I do about it?  My inclination is to convert the text fields to varchar(4000) and use <>, but is there a better way?

  declare @position int

  declare @oldstring varchar(4000)

  declare @newstring varchar(4000)

  set @position = 1

  Set @oldstring = 'Sent Amount: 75  Memo: cash bail plus 250.00 [pr bail pretrial confinement    '

  Set @newstring = 'Sent Amount: 75  Memo: cash bail plus 250.00 [pr bail pretrial confinement    '

  create table #unprintable

   (CharCodeOld int,

    CharCodeNew int,

    CharOld  char,

    CharNew  char

  )

  while @position <=80

  begin

   insert into #unprintable

    select 

      ASCII(SUBSTRING(@oldstring, @position, 1)),

      ASCII(SUBSTRING(@newstring, @position, 1)),

         CHAR(ASCII(SUBSTRING(@oldstring, @position, 1))),

         CHAR(ASCII(SUBSTRING(@newstring, @position, 1)))

      SET @position = @position + 1

  end

  select * from #unprintable where CharOld not like CharNew

  select * from #unprintable where CharOld <> CharNew

  drop table #unprintable

  Thanks,

  Mattie

• allen davidson

  SSCertifiable

  Points: 7042

  More actions

  March 23, 2006 at 8:04 am

  #628358

  Q1 only. I'm guessing it's because [ is part of the patern definition in the LIKE predicate so

  select * from #unprintable where CharOld not like '[[]'

  does not find your [ row wheras

  select * from #unprintable where CharOld not like '['

  does return the row

   

   

• SQLBill

  SSC Guru

  Points: 51440

  More actions

  March 23, 2006 at 11:29 am

  #628430

  LIKE means SIMILAR TO and accepts wildcards. [ can be used as a wildcard.

  means NOT EQUAL TO

  -SQLBill

• MattieNH

  SSCertifiable

  Points: 7463

  More actions

  March 27, 2006 at 6:42 am

  #628868

  Thanks to both of you.  The only wildcard character I use with LIKE is '%', so I tend to forget the others.  Because I knew the original data fields were nowhere near 4000 characters long, casting to varchar(4000)  and using '<>' solved my problem. 

  Mattie