3
\$\begingroup\$

I view users by city using this SQL query:

SELECT
    ROW_NUMBER() OVER(ORDER BY Reputation DESC) AS [#], 
    Id AS [User Link], 
    Reputation
FROM
    Users
WHERE
 LOWER(Location) LIKE 'sulaimani, iraq' or LOWER(Location) LIKE 'erbil, iraq'
ORDER BY
    Reputation DESC;

This works.

I don't like the repetition of the LIKE clause - this could get unwieldy if I want to include many more locations (but not the whole country).

\$\endgroup\$
1
  • 2
    \$\begingroup\$ Does SEDE not support LOWER(Location) IN ('sulaimani, iraq', 'erbil, iraq')? That would be a little bit more manageable. I think that the Location value is free-form anyway, so could be difficult unless users are consistent and don't make spelling errors! \$\endgroup\$ Aug 28 at 11:48
4
\$\begingroup\$
 LOWER(Location) LIKE 'sulaimani, iraq' or LOWER(Location) LIKE 'erbil, iraq'

It is not necessary to use LIKE if you want an exact match. The = is sufficient in that case.

 LOWER(Location) = 'sulaimani, iraq' or LOWER(Location) = 'erbil, iraq'

And as @TobySpeight already commented, you can then change it to an IN.

 LOWER(Location) IN ('sulaimani, iraq', 'erbil, iraq')

That won't work if you want to actually use LIKE to do a fuzzier match, e.g.

 LOWER(Location) LIKE '%, iraq'

But in that case, you could create a temporary table with the data as you want and query that, possibly joining the original table. By "data as you want", I mean lowercased and possible only the part after the ", " in this case. For other queries, you might have different requirements.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.