John Davidson

Message:

So i have 2 RAW Substring indexes in my query builder which i need to join together. These values are used for exporting to excel.

So this is the code i have.

public function collection()

{

        'bookings.user_fullname',

        DB::raw("SUBSTRING_INDEX(user_fullname, ' ', 1) AS Firstname"),

        DB::raw("SUBSTRING_INDEX(user_fullname, ' ', 2) AS MiddleName"),

        DB::raw("SUBSTRING_INDEX(SUBSTRING_INDEX(user_fullname, ' ', 3),' ',-1) AS Lastname"),

}

So i want the middle name and last name to be joined together instead of them being on seperate columns. For example a name "John Oliver Smith" would be seperated as "John" and "Oliver Smith". Or even a longer name like "Mark Andrew Philip Man" as "Mark" and "Andrew Philip Man".

So basically name on one column and the rest of the values on different column. Any idea how i can do that?

---

My Answer:
You can achieve this by using CONCAT function in your query builder. Here's how you can modify your code to join the middle name and last name together:

php
public function collection()
{
'bookings.user_fullname',
DB::raw("SUBSTRING_INDEX(user_fullname, ' ', 1) AS Firstname"),
DB::raw("CONCAT(SUBSTRING_INDEX(user_fullname, ' ', 2), ' ', SUBSTRING_INDEX(SUBSTRING_INDEX(user_fullname, ' ', 3),' ',-1)) AS MiddleLastName"),
}


With this modification, the `MiddleLastName` column will contain the middle name and last name joined together.