eloquent - How to use Nested functions substr(), cast() and Max() in query builder in laravel 8?

Question

I posted this question yesterday but I think it's unclear so I deleted it and posted it again with more details. In my oracle database I have a USERS table with id_user defined as varchar and this varchar is like this: '145/1' ...... '145/9' so to add a new user I check the maximum value ('145/9') and add 1 to the second part of id_user (after the slash) so that the id_user is '145/10'.

The steps are like this:

1. First: I'm using substr() to get the second part (after the slash) of all id_user.
2. Second: I use Cast() to convert it to Int.
3. Third: I use Max() to get the maximum value of Int numbers.

Finally in my laravel code I use the result of this query(the result is 9) and add 1 to it and insert a new user in the users table with id_user = '145/10' and so on. This query works fine but I need it in the Query Builder so I am trying a lot of queries but they didn't work.(please help me)

SELECT MAX(CAST(SUBSTR(id_user, INSTR (id_user, '/') + 1) AS INT)) AS Aggregate 
FROM "users" 
WHERE "ID_USER" LIKE '145/%';

Answer 1

Finally, this query gives me the correct maximum:

 DB::table('users')->select(DB::raw('MAX(CAST(SUBSTR(id_user,INSTR(id_user, '/') + 1) AS INT)) as max')) ->where('id_user','like','145'.'/%')->get()[0];