Query to find users from where?

Question

Based on my question here, I have updated my query to take user input so it can be a generic query:

DECLARE @location AS nvarchar(max) = ##location:string##;
SELECT u.Id [User Link], u.Reputation, u.Location
FROM Users u
WHERE lower(u.Location) LIKE lower('%' + @location + '%')
ORDER BY 'Reputation' DESC

Is there something I should be doing differently, or is this good?

• nhgrif: As d347hm4n's answer points out, we should be careful about our column types. Users.Location is NVarChar(100).

Answer 1

This looks fine, the only thing I would say is @location is being declared as nvarchar(max). I can only assume that u.Location is also nvarchar(max). If it isn't then set it to be the same size. nvarchar max will have a slight performance hit to it.

Answer 2

There's no need to add a table alias when you are only dealing with a single table.

Also there's no need to worry about LOWER as the columns have a case insensitive collation.

Fixing the datatype issue mentioned in the comments and removing the unnecessary other stuff mentioned above you would end up with.

DECLARE @location AS NVARCHAR(100) = ##location:string##;
SELECT Id AS [User Link],
 Reputation,
 Location
FROM Users
WHERE Location LIKE '%' + @location + '%'
ORDER BY Reputation DESC 

One other thing to consider is if the input itself might contain any characters of special significance in the LIKE pattern syntax.

It is unlikely that any legitimate locations will contain characters such as %, _, [.

If you do want to correctly support searches for these strings you would need to either ESCAPE them all or just use CHARINDEX instead.

SELECT Id AS [User Link],
 Reputation,
 Location
FROM Users
WHERE CHARINDEX(@location, Location) > 0
ORDER BY Reputation DESC