I have a database with 2 columns, with the name of the players written in this format:

I should find all the rows containing the name of the players but only with the surname, like Yang, without Y.

So I thought to use the LIKE query, like this:

for game in final_table:
  name = "Yang"
  cur1.execute("SELECT * FROM matches WHERE Player1 like ?", [name])
  match = cur1.fetchall()

print(match)

It gives me nothing in return. Instead if I write the entire name like this:

for game in final_table:
  name = "Yang Y."
  cur1.execute("SELECT * FROM matches WHERE Player1 like ?", [name])
  match = cur1.fetchall()

print(match)

It works properly. What am I doing wrong?

CodePudding user response：

SQLite provides two wildcards for constructing patterns. They are percent sign % and underscore _ :

The percent sign % wildcard matches any sequence of zero or more characters. The underscore _ wildcard matches any single character.

for game in final_table:
  name = "Yang %"
  cur1.execute("SELECT * FROM matches WHERE Player1 like ?", [name])
  match = cur1.fetchall()

print(match)

CodePudding user response：

Take a look at the documentation for LIKE. The way you've written your query in both cases attempts to find exact matches against name. There will be no exact matches because all of the data in your SQLite database has spaces and additional text after that last name string.

Instead, you might want to write:

cur1.execute("SELECT * FROM matches WHERE Player1 like ? ", [f"{name} %"])

Notice the use of the "%" wildcard that'll match whatever initials follow the surname.