Help using like operator

• iiit.raju

  Old Hand

  Points: 348

  More actions

  April 14, 2013 at 11:06 pm

  #276493

  I have a table

  create table accounts

  (

  accname varchar(100),

  recstatus bit

  )

  and records are as follows:

  Insert into accounts values ('Pepsi Co',0)

  Insert into accounts values ('Pepsi Co',0)

  Insert into accounts values ('Prudential',1)

  Insert into accounts values ('Lennox International',1)

  Insert into accounts values ('Eurosystems (Harveynash Inc) [OLD]',0)

  Insert into accounts values ('ETJ Holdings, inc. [OLD]',0)

  Insert into accounts values ('I-nnovate [OLD]',1)

  Insert into accounts values ('Iflowsoft, LLC [OLD]',1)

  Insert into accounts values ('1st OBJECT Inc [OLD]',0)

  Insert into accounts values ('Jet Aviation Holdings, Inc [OLD]',0)

  Now i want to get the values of the table whose accname contains [old].So please help me

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 14, 2013 at 11:38 pm

  #1606350

  iiit.raju (4/14/2013)

  ---

  I have a table

  create table accounts

  (

  accname varchar(100),

  recstatus bit

  )

  and records are as follows:

  Insert into accounts values ('Pepsi Co',0)

  Insert into accounts values ('Pepsi Co',0)

  Insert into accounts values ('Prudential',1)

  Insert into accounts values ('Lennox International',1)

  Insert into accounts values ('Eurosystems (Harveynash Inc) [OLD]',0)

  Insert into accounts values ('ETJ Holdings, inc. [OLD]',0)

  Insert into accounts values ('I-nnovate [OLD]',1)

  Insert into accounts values ('Iflowsoft, LLC [OLD]',1)

  Insert into accounts values ('1st OBJECT Inc [OLD]',0)

  Insert into accounts values ('Jet Aviation Holdings, Inc [OLD]',0)

  Now i want to get the values of the table whose accname contains [old].So please help me

  Looking at Books Online (this isn't something I knew off the top of my head), this should work:

  Select *

  from dbo.accounts

  where accname like '%![OLD]%' escape '!';

• Pankaj067

  Old Hand

  Points: 313

  More actions

  April 15, 2013 at 12:50 am

  #1606354

  This query can also be answered without using escape

  The query is shown below:

  select * from accounts where accname like '%OLD%'

  Thanks:-)

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  April 15, 2013 at 3:22 am

  #1606387

  pankaj2.kumar (4/15/2013)

  ---

  This query can also be answered without using escape

  The query is shown below:

  select * from accounts where accname like '%OLD%'

  Except that would also match entries such as 'Jet Aviation Holdings, Inc', not just ones with OLD within the [], hence can give incorrect results.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• iiit.raju

  Old Hand

  Points: 348

  More actions

  April 15, 2013 at 3:26 am

  #1606390

  @thank you pettis

• kapil_kk

  SSC-Insane

  Points: 21316

  More actions

  April 15, 2013 at 6:14 am

  #1606436

  GilaMonster (4/15/2013)

  ---

  pankaj2.kumar (4/15/2013)

  ---

  This query can also be answered without using escape

  The query is shown below:

  select * from accounts where accname like '%OLD%'

  Except that would also match entries such as 'Jet Aviation Holdings, Inc', not just ones with OLD within the [], hence can give incorrect results.

  Right Gail , Pankaj query will not work correctly..

  _______________________________________________________________
  To get quick answer follow this link:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/