SQL LIKE not functioning with space after query [duplicate]

Question

I'm trying to make search functionality to output all available books based on author/title or genre typed in a form (I'm using sqlite), using LIKE function.

The piece of code I'm using is as follows:

# User reached route via POST (submitting the form)
else:
    query = request.form.get("query")

    # Ensure query is not blank
    if not query:
        return apology("you haven't typed anything!", 400)

    # Implementing search
    books = db.execute("SELECT author, title, genre, id FROM books WHERE booked = 0 AND (author LIKE '%' || :query || '%' OR title LIKE '%' || :query || '%' OR genre LIKE '%' || :query || '%')",
                      query = query)

However, users often type query followed by a space (especially on mobile devices). And in this case the code doesn't work.

How can I account for the situation with a space after the query and fix this?
Is it possible to account for infinite number of spaces? E.g. if I print 2 spaces after?

Thanks!

Use strip(): query = request.form.get("query").strip() to remove any leading or trailing spaces. — forpas
– forpas, Commented Dec 6, 2020 at 11:30

Cem · Accepted Answer · 2020-12-06 11:40:21Z

3

If you don't want white space at either end of the query, you can trim the input:

query = request.form.get("query").strip();

This also makes sure if not query part is executed when the query consists solely of white space.

answered Dec 6, 2020 at 11:40

Cem

1,2966 silver badges18 bronze badges

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

Collectives™ on Stack Overflow

SQL LIKE not functioning with space after query [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related