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:
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/%';
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];