help with WHERE clause

kenniejaydavis

I have a situation where I want to be able to match an exact string value and if that is unsuccessful then match all strings like the search parameter. The scenario I'm working on right now is searching for users by username.

An example for simplicities sake is if I have the user "admin" and "administrator" in a database, if I search for "admin", I only want that single user to be returned, however if I searched for "adm", I want both users to be returned.

Here's the approach I've taken so far:

SELECT *

FROM Users

WHERE

((@Username IS NULL) OR ((Username = @Username) OR (Username LIKE N'%' + @Username + N'%')))

I expected that if (Username = @Username) was found, a single row would be returned and the rest of the clause would be ignored. However, it seems as though the LIKE is still being executed because if I search for "admin", I still get two rows returned even though I only want the single row with the exact username.

What do I need to do to achieve the results I'm looking for

Chris Howarth

Try the code below.

Chris

Code Block

SELECT *
FROM Users
WHERE
((@Username IS NULL)
OR ((Username = @Username)
OR (Username LIKE N'%' + @Username + N'%'
AND NOT EXISTS (SELECT 1
FROM Users u2
WHERE u2.Username = @Username))))

Code Block

--Example with results

DECLARE @Users TABLE
(ID INT IDENTITY(1, 1), Username VARCHAR(20))

INSERT INTO @Users
SELECT 'Admin' UNION ALL
SELECT 'Administrator' UNION ALL
SELECT NULL

DECLARE @Username VARCHAR(20)

SET @Username = NULL

SELECT *
FROM @Users
WHERE
((@Username IS NULL)
OR ((Username = @Username)
OR (Username LIKE N'%' + @Username + N'%'
AND NOT EXISTS (SELECT 1
FROM @Users u2
WHERE u2.Username = @Username))))

/*
ID Username
1 Admin
2 Administrator
3 NULL
*/

SET @Username = 'Adm'

SELECT *
FROM @Users
WHERE
((@Username IS NULL)
OR ((Username = @Username)
OR (Username LIKE N'%' + @Username + N'%'
AND NOT EXISTS (SELECT 1
FROM @Users u2
WHERE u2.Username = @Username))))

/*
ID Username
1 Admin
2 Administrator
*/

SET @Username = 'Admin'

SELECT *
FROM @Users
WHERE
((@Username IS NULL)
OR ((Username = @Username)
OR (Username LIKE N'%' + @Username + N'%'
AND NOT EXISTS (SELECT 1
FROM @Users u2
WHERE u2.Username = @Username))))

/*
ID Username
1 Admin
*/

kenniejaydavis

More SQL than I wanted to write but it works. Thanks!

help with WHERE clause

Re: help with WHERE clause

Re: help with WHERE clause