Applying string function in column Laravel 5.4

Question

I'm using the latest Laravel 5.4

I am trying to make a simple query to search users by name. The query written for MySQL looks like this:

SELECT *
FROM users
WHERE upper(name) LIKE '%FOO%';

I'm trying to make it work with Eloquent. Things I've tried but failed:

User::where('upper(name)', 'LIKE', '%FOO%')->get()
DB::table('users')->where('upper(name)', 'LIKE', '%FOO%')->get()

Both fail with the following error:

Illuminate\Database\QueryException with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'upper(name)' in 'where clause' (SQL: select * from users where upper(name) LIKE %FOO%)'

The query seems to fail because Eloquent wraps the upper(email) statement with backticks (" ` ", " ` "). Is there a way to go around this issue or do I have to use a particular eloquent function to get convert a column to uppercase, lowercase, e.t.c?

Niklesh Raut · Accepted Answer · 2017-03-11 10:53:20Z

11

Use DB::raw()

User::where(DB::raw('upper(name)'), 'LIKE', '%FOO%')->get()

It would generate query like this

"select * from `users` where upper(name) LIKE ?"

edited Mar 11, 2017 at 10:53

answered Mar 11, 2017 at 10:44

Niklesh Raut

34.9k17 gold badges82 silver badges112 bronze badges

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

Comments

Muthu17 · Accepted Answer · 2017-03-11 10:44:06Z

3

You can use whereRaw() in laravel to achieve this :

  User::whereRaw("upper(name) LIKE '%FOO%'")
                   ->get();

answered Mar 11, 2017 at 10:44

Muthu17

1,54112 silver badges20 bronze badges

2 Comments

dimlucas Over a year ago

Does this protect against SQL Injection or do I need to escape the passed string manually?

Muthu17 Over a year ago

Nope. its nor against SQL Injection.

Collectives™ on Stack Overflow

Applying string function in column Laravel 5.4

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related