Arranging data by last name when the first and last names are combined in a single field of the

Question

Arranging data by last name when the first and last names are combined in a single field of the

My datatable is set up with basic fields and data rows, including a column for customer names that combine both first and last names. Is there a way to modify the sorting method in datatables to sort based on the surname (last word) within this column?

javascript datatables

Answer 1

Answer №1

To accomplish this nowadays, all you have to do is add a data-sort attribute to your <td>, like so:

<td data-sort="Johnson Dwayne">Dwayne "The Rock" Johnson</td>

When using Datatables, it will automatically handle this in the expected manner. For more information, you can refer to their guide on HTML5 data-* attributes.

Answer 2

To accomplish this nowadays, all you have to do is add a data-sort attribute to your <td>, like so:

<td data-sort="Johnson Dwayne">Dwayne "The Rock" Johnson</td>

When using Datatables, it will automatically handle this in the expected manner. For more information, you can refer to their guide on HTML5 data-* attributes.

Answer 3

Answer №2

Although utilizing a custom sort function is the recommended approach, I advise against splitting the name on a space as it may yield inaccurate results in certain scenarios.

DataTables provides the capability to associate arbitrary data with rows for sorting purposes even if it is not visible, so consider attaching the actual last name independently.

Alternatively, you can pass the first and last name separately and then use a custom renderer to concatenate them for display within a single column.

Answer 4

Although utilizing a custom sort function is the recommended approach, I advise against splitting the name on a space as it may yield inaccurate results in certain scenarios.

DataTables provides the capability to associate arbitrary data with rows for sorting purposes even if it is not visible, so consider attaching the actual last name independently.

Alternatively, you can pass the first and last name separately and then use a custom renderer to concatenate them for display within a single column.

Answer 5

Answer №3

If you find yourself in need of a single column for sorting by last name in a database system like WordPress or any other PHP application, consider this simple suggestion: Instead of two separate columns for first and last names, combine them into one 'Name' column with the format:

<td>last-name first-name last-name</td>

By structuring the data this way, sorting by last name becomes possible within the same column.

To achieve this, include the last name only once as follows:

<span class="display-none">last-name</span>

While this method may be considered a workaround or hack, it provides a quick and effective solution to the problem at hand.

Answer 6

If you find yourself in need of a single column for sorting by last name in a database system like WordPress or any other PHP application, consider this simple suggestion: Instead of two separate columns for first and last names, combine them into one 'Name' column with the format:

<td>last-name first-name last-name</td>

By structuring the data this way, sorting by last name becomes possible within the same column.

To achieve this, include the last name only once as follows:

<span class="display-none">last-name</span>

While this method may be considered a workaround or hack, it provides a quick and effective solution to the problem at hand.

Answer 7

Answer №4

If you were looking to address this at the database level, you would need to include a Substring function in the sorting criteria of your query, with the specific syntax varying based on the type of database being used.

For MSSQL:

ORDER BY SUBSTRING(COLUMN, CHARINDEX(' ', COLUMN), 50);

For MYSQL:

ORDER BY Substring(COLUMN, Position(' ' IN COLUMN), 50);

Answer 8

If you were looking to address this at the database level, you would need to include a Substring function in the sorting criteria of your query, with the specific syntax varying based on the type of database being used.

For MSSQL:

ORDER BY SUBSTRING(COLUMN, CHARINDEX(' ', COLUMN), 50);

For MYSQL:

ORDER BY Substring(COLUMN, Position(' ' IN COLUMN), 50);

Answer 9

Answer №5

Update
The initial response provided a generic sorting function, overlooking the datatables tag. The revised answer addresses the use of datatables.

As mentioned by @Stephen P in the comments, you can configure datatables to sort based on a hidden column. You can create a hidden column for surnames only and then set the ordering of the first column (full name) based on the second column (surname). I implemented JavaScript to generate the surname column from the full name, but for larger datasets, it's recommended to handle this server-side.

$(document).ready(function() {
    // Find the full name, extract the surname, and add it as the second column in the table
    $('#example td:first-child').each(function() {
      $('<td>'+$(this).text().split(' ')[1]+'</td>').insertAfter($(this));
    });
    // Configure sorting
    $('#example').DataTable( {
        'columnDefs': [
          {'orderData':[1], 'targets': [0]},
          {
            'targets': [1],
            'visible': false,
            'searchable': false
          },
        ],
    } );
});

Name	Surname	Position	Office	Age	Start date	Salary
Name	Surname	Position	Office	Age	Start date	Salary
Tiger Nixon	System Architect	Edinburgh	61	2011/04/25	$320,800
Garrett Winters	Accountant	Tokyo	63	2011/07/25	$170,750
Ashton Cox	Junior Technical Author	San Francisco	66	2009/01/12	$86,000
Cedric Kelly	Senior Javascript Developer	Edinburgh	22	2012/03/29	$433,060
Airi Satou	Accountant	Tokyo	33	2008/11/28	$162,700
Brielle Williamson	Integration Specialist	New York	61	2012/12/02	$372,000
Herrod Chandler	Sales Assistant	San Francisco	59	2012/08/06	$137,500

Answer 10

Update
The initial response provided a generic sorting function, overlooking the datatables tag. The revised answer addresses the use of datatables.

As mentioned by @Stephen P in the comments, you can configure datatables to sort based on a hidden column. You can create a hidden column for surnames only and then set the ordering of the first column (full name) based on the second column (surname). I implemented JavaScript to generate the surname column from the full name, but for larger datasets, it's recommended to handle this server-side.

$(document).ready(function() {
    // Find the full name, extract the surname, and add it as the second column in the table
    $('#example td:first-child').each(function() {
      $('<td>'+$(this).text().split(' ')[1]+'</td>').insertAfter($(this));
    });
    // Configure sorting
    $('#example').DataTable( {
        'columnDefs': [
          {'orderData':[1], 'targets': [0]},
          {
            'targets': [1],
            'visible': false,
            'searchable': false
          },
        ],
    } );
});

Name	Surname	Position	Office	Age	Start date	Salary
Name	Surname	Position	Office	Age	Start date	Salary
Tiger Nixon	System Architect	Edinburgh	61	2011/04/25	$320,800
Garrett Winters	Accountant	Tokyo	63	2011/07/25	$170,750
Ashton Cox	Junior Technical Author	San Francisco	66	2009/01/12	$86,000
Cedric Kelly	Senior Javascript Developer	Edinburgh	22	2012/03/29	$433,060
Airi Satou	Accountant	Tokyo	33	2008/11/28	$162,700
Brielle Williamson	Integration Specialist	New York	61	2012/12/02	$372,000
Herrod Chandler	Sales Assistant	San Francisco	59	2012/08/06	$137,500

Arranging data by last name when the first and last names are combined in a single field of the

Answer №1

Answer №2

Answer №3

Answer №4

Answer №5

Similar questions

Angular 2+ enables the creation of dynamic folders and allows for uploading multiple files to the server in a seamless

What is the process for executing a Js file on a website?

Using JQuery to fill a drop-down menu based on the selection in another drop-down menu

Tips for employing e.preventDefault within the onChange event handler for a select element

Utilize PHP server to serve index.html for all routes with the combination of React and react-router-dom

Jquery UI - selection buttons

Retrieving the body of a GET request using NodeJS and axios

TS object encountering a restriction with an inaccessible method

Implementation of Gallows Game

Should we employ getAttribute() or avoid it entirely? That is the ultimate query

Execution of scripts upon completion of document loading via AJAX

Guide on utilizing nodeJS libraries to retrieve data from a webAPI

Transfer the information of a selected element to a different element

Angular 6: Endlessly Scroll in Both Directions with Containers

The Front End API is unable to locate the Back End Endpoint

What is the best way to check if an email address already exists in a PHP MySQL database?

The Facebook bots are unable to crawl our AngularJS application because the JavaScript is not being properly

How to manage events for geometries in Three.js?

How can I use JavaScript to enable the functionality of "Open in Safari"?

What is the best approach to return an Observable value only if it is not null, otherwise invoke an HTTP service to fetch and return the