jQuery DataTables - Filter column by exact match

Trying to only display exact matches to the search term entered in the search bar.

For instance, I have a search bar that filters by ID#. I want only records that match the exact # entered to display.

So if 123 is entered, I don't want 12345, 91239, etc etc to be displayed. Only 123.

Saw some info about bRegex on the FAQ page, but it's not working for me. Any ideas?

10 Answers

This will give you exact result for a column.

 table.column(i) .search("^" + $(this).val() + "$", true, false, true) .draw(); 

ie . search( input , regex, smart , caseInsen )

Ok solved the problem. However, since the column I am using the exact match on sometimes contains multiple ID #s seperated by commas, I wont be able to use an exact match search.

But for those interested, here is the answer:

oTable.fnFilter( "^"+TERM+"$", COLUMN , true); //Term, Column #, RegExp Filter 

$(document).ready( function() { $('#example').dataTable( { "oSearch": {"bSmart": false} } ); } ) 

Try using the bSmart option and setting it to false

From the documentation

"When "bSmart" DataTables will use it's smart filtering methods (to word match at any point in the data), when false this will not be done."

UPDATE

I found this:

oSettings.aoPreSearchCols[ iCol ].sSearch = "^\\s*"+'1'+"\\s*$"; oSettings.aoPreSearchCols[ iCol ].bRegex = false; oSettings.aoPreSearchCols[ iCol ].bSmart= false; 

at this link

looks like you can set bSmart and bRegex per column as well as specifying a manual regex per column.

You can use regular expression for exact matching as following:

var table = $('#dt').DataTable(); $('#column3_search').on('keyup', function () { // Note: column() accepts zero-based index meaning the index of first column is 0, second column is 1 and so on. // We use `2` here as we are accessing 3rd column whose index is 2. table.column(2) .search("^" + this.value + "$", true, false, true) .draw(); }); 

The syntax of the search function is:

search(input, regex, smart_search, case_insensitive)

We disable smart search in this case because search function uses regular expression internally when smart search is set to true. Otherwise, this creates conflict between our regular expression and the one that is used by search function.

For more information, check out the following documentation from DataTable:

column().search()

Hope it's helpful!

If you want the exact match from the beginning you can try this code,

 var table = $('#myTable').DataTable() $('#filterrow > th:nth-child(2) > input').on( 'keyup change', function () { table .column( $(this).parent().index()+':visible' ) .search( "^" + this.value, true, false, true ) .draw(); } ); 

just set the regex and smart false. and you get the exact result.

 $('#yourTableID').DataTable({ search: { regex: false, smart: false } }) 

$(document).ready(function() { tbl = $('#example').dataTable(); tbl.fnFilter("^" + filter_value + "$"); }); 

Where filter_value is the string entered in the filter field.

Regex was not a handy solution for me as it require lot of exceptions in the code. So, my solution was to add in the jquery.datatable.min.js a new option 'exactvalue' with default value false (to avoid compatibility problems)

 [...] p.columns.push({data:n,name:u.sName,searchable:u.bSearchable,exactvalue:u.bExactvalue,orderable:u.bSortable, [...] d.bFilter&&(m("sSearch_"+l,sa.sSearch),m("bRegex_"+l,sa.bRegex),m("bSearchable_"+l,u.bSearchable),m("bExactvalue_"+l,u.bExactvalue)); [...] q.models.oColumn={idx:null,aDataSort:null,asSorting:null,bSearchable:null,bExactvalue:null,bSortable:null,bVisible:null [...] q.defaults.column={aDataSort:null,iDataSort:-1,asSorting:["asc","desc"],bSearchable:!0,bExactvalue:false,bSortable:!0, [...] 

This new option will be sent along the other data in the post:

 [...] columns[5][searchable]: true columns[5][exactvalue]: true columns[5][orderable]: true [...] 

After that, change the php ssp class to accept the new value. Modify filter function in the ssp changing:

 if ( $requestColumn['searchable'] == 'true' ) { if ( $requestColumn['exactvalue'] == 'true' ) { $binding = self::bind( $bindings, $str, PDO::PARAM_STR ); $columnSearch[] = ($isJoin) ? $column['db']." = ".$binding : "`".$column['db']."` = ".$binding; }else{ $binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR ); $globalSearch[] = ($isJoin) ? $column['db']." LIKE ".$binding : "`".$column['db']."` LIKE ".$binding; } } 

and repeat on the individual column filtering

 if ( $requestColumn['searchable'] == 'true' && $str != '' ) { if ( $requestColumn['exactvalue'] == 'true' ) { $binding = self::bind( $bindings, $str, PDO::PARAM_STR ); $columnSearch[] = ($isJoin) ? $column['db']." = ".$binding : "`".$column['db']."` = ".$binding; }else{ $binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR ); $columnSearch[] = ($isJoin) ? $column['db']." LIKE ".$binding : "`".$column['db']."` LIKE ".$binding; } } 

Now, in your table columnDefs definition, simply add

{'targets': 5, 'exactvalue': true} 

and your column will be filtered with exact value.

The current versions of Datatables supports using real exact matching on a column basis.

table.column(i) .search($(this).val(), false, false, false) .draw(); 

The documentation explains each flag.

table.column(col_num).search(filter_value + "$", true, true, false).draw();