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

Question

Post reply

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

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 · Answer 2

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: 53354 More actions · Answer 3

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