Laravel/PostgreSQL query with non-capitalised letters

Question

I am doing a Laravel CRUD exercise and I have set up a search route in the controller:

public function search($name)
    {
        return Product::where('name', 'like', '%'.$name.'%')->get();
    }

And on the backend psql database, I have an item name exactly as iPhone 11.

When I ran a query on postman, it worked fine if I GET by localhost:8000/api/products/search/iPh. However, if I uncapitalised it as ...search/iph, it would return an empty array.

So my question is, what do I do so I can search with uncapitalised letters while the data stored contains capitalised letters?

S N Sharma · Accepted Answer · 2021-08-26 05:55:13Z

1

For MySQL you can use raw query

Product::whereRaw("UPPER(name) LIKE '%'". strtoupper($name)."'%'")->get();

For pgSQL you can use ILIKE for case in-sensitive and LIKE for case sensitive

Product::where('name', 'ILIKE', '%'.$name.'%')->get();

answered Aug 26, 2021 at 5:55

S N Sharma

1,5212 gold badges10 silver badges22 bronze badges

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

2 Comments

Beaumont Over a year ago

Good tip! Do you have any recommendation on a cheat sheet for this type of query for mysql and pgsql?

S N Sharma Over a year ago

You can check Laravel Doc. There is not such kind of cheat sheet. Only few queries might not be compatible. You will find the solution for them easly.

Collectives™ on Stack Overflow

Laravel/PostgreSQL query with non-capitalised letters

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related