WHERE and LIKE Clause - 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

General SQL Server Forums

New to SQL Server Programming

WHERE and LIKE Clause

Author

Topic

wilshaw
Starting Member

10 Posts

Posted - 2012-01-07 : 08:25:06

I'm using the below sql to retrieve records using LIKE:-

@Airport varChar(20)
,@Sector varChar(20)

if @Airport = 'Any'
set @Airport = '%'
else
set @Airport = @Airport + '%'

if @Sector = 'Any'
set @Sector = '%'
else
set @Sector = @Sector + '%'

SELECT var1, var2, var3
FROM MyDataBase
WHERE
airport LIKE @Airport
AND
sector LIKE @Sector

Problem is if @Airport or @Sector is set to 'Any', it should return all rows, but it doesn't return any dbNull or records with empty values which I want.

Is there any other way to perform a WHERE query (also I've been told LIKE is not the best performing query)?

Many thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-07 : 08:32:27

[code]
SELECT var1, var2, var3
FROM MyDataBase
WHERE
(airport LIKE @Airport + '%' OR @Airport='Any'
AND
(sector LIKE @Sector + '%' OR @Sector = 'Any')
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wilshaw
Starting Member

10 Posts

Posted - 2012-01-07 : 08:45:37

Thanks, but this won't work as there are no records in the Airport or Sector columns containing the word 'Any'. If the parameter(s) are set to 'Any', I want all rows returning, including the records where Sector and Airport are NULL or an empty string.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-07 : 08:47:46

quote:
Originally posted by wilshaw

Thanks, but this won't work as there are no records in the Airport or Sector columns containing the word 'Any'. If the parameter(s) are set to 'Any', I want all rows returning, including the records where Sector and Airport are NULL or an empty string.

Thanks

can you please test it and then comment

Check the code
I'm never comparing fields to Any
I'm comparing only parameter values to Any

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-07 : 08:50:16

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

Visakh's solution will work, but it's a performance nightmare on larger rowcounts.

--
Gail Shaw
SQL Server MVP

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-07 : 08:51:42

Visakh may have missed a right bracket (see below). But, that should work. He is not trying to look for Airports that have 'Any' as part of the name. He is looking whether the parameter (@airport) is 'Any'

SELECT var1,
       var2,
       var3
FROM   MyDataBase
WHERE  (airport LIKE @Airport + '%' OR @Airport = 'Any')
       AND 
       (sector LIKE @Sector + '%' OR @Sector = 'Any')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-07 : 08:53:35

quote:
Originally posted by GilaMonster

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

Visakh's solution will work, but it's a performance nightmare on larger rowcounts.

--
Gail Shaw
SQL Server MVP

yeah thats true
for larger datasets the solution using dynamic sql might be preferable

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wilshaw
Starting Member

10 Posts

Posted - 2012-01-07 : 09:04:39

Hi - thanks, I have tested it but it doesn't return the rows where sector or airport are NULL.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-07 : 09:09:06

quote:
Originally posted by wilshaw

Hi - thanks, I have tested it but it doesn't return the rows where sector or airport are NULL.

why?
what were you passing for @Airport and @Sector?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wilshaw
Starting Member

10 Posts

Posted - 2012-01-07 : 09:34:10

Hi - thanks everyone, I've used the response from GilaMonster on dynamic sql which is working for me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources