writing a query to search all columns at the same time(like a Wildcard search)

• Patrick.I

  Hall of Fame

  Points: 3105

  More actions

  August 20, 2009 at 2:00 am

  #134883

  I have table like below

  CREATE TABLE [dbo].[log](

  [rowid] [bigint] IDENTITY(1,1) NOT NULL,

  [log_number] [int] NOT NULL,

  [username] [nvarchar](50) NULL,

  [number] [nvarchar](50) NULL,

  [sys] [nvarchar](50) NULL,

  [func] [nvarchar](50) NULL,

  [draised] [datetime] NULL,

  [dresolved] [datetime] NULL,

  [storec] [nvarchar](50) NULL,

  [lstatus] [nvarchar](50) NULL,

  [inserteddate] [smalldatetime] NOT NULL CONSTRAINT [DF_problem_log_date_inserted] DEFAULT (getdate())

  ) ON [PRIMARY]

  when i try and search for the whole LOT like below i don't get adequate results sometimes i get nothing despite the fact that the data is there

  What i want it is a WILDCARD search so i decided to use LIKE '%%' but i have problem when the column is an int and with date columns too

  SELECT [rowid]

  ,[log_number]

  ,[username]

  ,[number]

  ,[sys]

  ,[func]

  ,[draised]

  ,[dresolved]

  ,[storec]

  ,[lstatus]

  ,[inserteddate]

  FROM log

  where username like '%' + @username + '%'

  [sys] like '%' + @sys+ '%'

  [log_number] = @log_number

  and draised between

  @sdateraised and @edateraised

  Any ideas?

  Also with log_number i have to use NULL when its empty the same as date columns

  So i need a Dynamic query which i don't want to use

  Thanks in Advance

• Dave Ballantyne

  SSC-Dedicated

  Points: 33556

  More actions

  August 20, 2009 at 2:03 am

  #1041979

  Try this link

  http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

  ---

  
  Clear Sky SQL
  My Blog[/url]

• arun.sas

  SSChampion

  Points: 11831

  More actions

  August 20, 2009 at 2:44 am

  #1041986

  Hi,

  search with WILDCARD is working well in the varchar/char columns

  and if the @log_number is null then

  try this

  where

  username like ('%' + @username + '%')

  [sys] like ('%' + @sys+ '%')

  [log_number] = (case when @log_number is null then [log_number] else @log_number end)

  and draised between @sdateraised and @edateraised

• The Dixie Flatline

  SSC Guru

  Points: 53353

  More actions

  August 20, 2009 at 11:33 am

  #1042311

  You might also consider a view or cte that casts all your datetime columns as varchar. You realize of course that you can get no benefits from indexes over your datetime columns if you do. I second the motion that you read Gail's article.

  __________________________________________________

  Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
  Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills