Like query in Python - SQLite

Question

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

Player1	Player2
Yang Y.	Ying G.
Kim G.	Uin Y.

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?

Take some time to read about the operator LIKE: sqlitetutorial.net/sqlite-like — forpas
– forpas, Commented Jun 16, 2022 at 15:55

Diego Torres Milano · Accepted Answer · 2022-06-16 15:55:31Z

1

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)

answered Jun 16, 2022 at 15:55

Diego Torres Milano

69.9k9 gold badges116 silver badges145 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

romanows · Accepted Answer · 2022-06-16 16:00:56Z

1

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.

answered Jun 16, 2022 at 16:00

romanows

4884 silver badges12 bronze badges

Collectives™ on Stack Overflow

Like query in Python - SQLite

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related