1

I have a database containing all usernames. Now, I have a page where I can search for an user. At the moment I use the following SQL: 

SELECT uid, username, id, status 
FROM users 
WHERE `username` LIKE <search string>

Now, I have a record with the username Hattorius. But when I use that SQL syntax, and search for hatt. It doesn't give any results. How can I still make this work?

I searched some around, but nobody really had an answer to this.

Aaron Jonk
  • 473
  • 2
  • 7
  • 21
  • 1
    Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product (and your query is using non-standard SQL). Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using –  Jun 25 '19 at 07:46
  • Column collation? Did you forget the `%` wildcards? – jarlh Jun 25 '19 at 07:47
  • 2
    use %% – ArunKumar M N Jun 25 '19 at 07:48
  • Possible duplicate of [SQL 'LIKE' query using '%' where the search criteria contains '%'](https://stackoverflow.com/questions/10803489/sql-like-query-using-where-the-search-criteria-contains) – Amira Bedhiafi Jun 25 '19 at 07:52
  • Is your real problem that it doesn't find the upper-case H when you search for lower-case h? Hattorius vs hattorius? If so you can either store a lower-case version of the name for searches (portable but duplicates data) or you can convert both values to lower-case when you search (probably not portable). For example "where lower(username) like lower()". – ewramner Jun 25 '19 at 07:56

2 Answers2

7

Try to use LIKE : 

SELECT uid, username, id, status 
FROM users 
WHERE `username` LIKE '%hatt%'
Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60
Jitendra G2
  • 1,196
  • 7
  • 14
5

Remove the single quotes:

SELECT uid, username, id, status 
FROM users 
WHERE username LIKE '%hatt%'
SmartestVEGA
  • 8,415
  • 26
  • 86
  • 139