How To Concat Or Insert Special Characters On To Query

So, I am looking for a way that I can insert special characters into my query such as & or a ","(comma) when it is returned

Currently when I do my query like this

$clientName = Client::query()
                    ->select('clients.first_name', 'clients.last_name', 'clients.spouse_first_name' , DB::raw("CONCAT(clients.last_name,' ',clients.first_name,'  ',clients.spouse_first_name) as full_name"))
                    ->where('id', $request->client_id)
                    ->get();

        return response($clientName);

It returns Smith John Jane

But how could I make my returned response be Smith, John & Jane and also to make it more dynamic, if there is no spouse how could I drop the &?

Talinon

7 years ago

Best Answer

Level 51

Why not handle this outside of the query?

Something like:

$client = Client::findOrFail($request->id);

$clientName = "{$client->last_name}, {$client->first_name}";

if (!is_null($client->spouse_first_name))
    $clientName .= " & {$client->spouse_first_name}";
    

        return response($clientName);

Or even better, add some model methods so this is always available:

Client.php

public function fullName() {

    return "{$this->last_name}, {$this->first_name}";

}

public function fullNameWithSpouse()
{

    if (!is_null($this->spouse_first_name))
        return $this->fullName() . " & " . $this->spouse_first_name;

    return $this->fullName();  // if no spouse

}

so you could simplify your original query with:

$client = Client::findOrFail($request->id);

return repsonse($client->fullNameWithSpouse());

boldstar

7 years ago

Level 2

@talinon , that is exactly what I was looking for! thank you for the help.

Please or to participate in this conversation.