jQuery DataTables - Filter column by exact match

Question

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?

score 53 · Answer 1 · answered May 29 '14 at 10:44

53

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 )

answered May 29 '14 at 10:44

Neeno Xavier

551
1
4
3

In my case, I am passing multiple values for search and this solution is not working for me. Can you help me over here? – Savan Gadhiya Apr 23 '20 at 13:12
1

Breaks the moment you have a special character in your input, (e. g. "+", "(", "!", etc) which isn't that uncommon. Rule Nr. 1: NEVER WORK WITH UNSANITIZED INPUT! – Robin93K Sep 03 '21 at 15:08

score 26 · Accepted Answer · answered Dec 22 '11 at 22:06

26

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

answered Dec 22 '11 at 22:06

JimmyJammed

9,598
19
79
146

Hi sir, I'm having the same problem. I was wondering where should I place the code you provided? Because I want to try it if it will solve my problem too. Hope you'll reply. – Louie May 11 '16 at 15:27
Hey, I was able to solve the problem of exact match on multiple ID's separated by commas. Here is a working fiddle for any one else who encounters the problem. http://jsfiddle.net/digvijayad/0m94e2v7/ Note: For my case, ids were unique across rows, i.e when searched only 1 row would return. – Digvijayad May 24 '19 at 10:56

score 11 · Answer 3 · edited Mar 31 '15 at 11:47

11

$(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 http://www.datatables.net/forums/discussion/4096/filtering-an-exact-match/p1

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

edited Mar 31 '15 at 11:47

davidkonrad

83,997
17
205
265

answered Dec 22 '11 at 20:55

Keith.Abramo

6,952
2
32
46

Not quite what I am looking for. So basically I have 3 custom search fields I use to filter the table. I only want one of the fields to use an exact search. For the other fields, I want them to still use the smart search. – JimmyJammed Dec 22 '11 at 21:54
Also on a side note, the bSmart basically means if I type in "John Smith", it will find any combination of those words (i.e. "Smith John", "John Jack Smith", etc etc.) – JimmyJammed Dec 22 '11 at 21:57

score 4 · Answer 4 · answered Aug 09 '18 at 22:29

4

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!

answered Aug 09 '18 at 22:29

Wolverine

1,712
1
15
18

In my case, I am passing multiple values for search and this solution is not working for me. Can you help me over here? – Savan Gadhiya Apr 23 '20 at 13:06
Can you explain what exactly you are trying to do? – Wolverine Apr 23 '20 at 16:23
in the example given by you - this.value is a single value. Instead of that, I am passing multiple values i.e. (1|2|3) in search function. – Savan Gadhiya Apr 23 '20 at 16:46
Multiple values are from the different input fields? – Wolverine Apr 23 '20 at 18:29
Does this approach work when column value contains `^`, `$` or other regex-reserved characters (like `.`)? – izogfif Dec 01 '20 at 08:05
1

@izogfif https://stackoverflow.com/questions/32846782/how-to-escape-special-characters-in-regular-expressions. Check this out. You can do that escaping regex reserved characters. That way, you can filter out the values easier. – Wolverine Dec 02 '20 at 17:15

score 3 · Answer 5 · answered Jan 10 '20 at 10:54

3

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

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

answered Jan 10 '20 at 10:54

anilam

694
6
7

In my case, I am passing multiple values for search and this solution is not working for me. Can you help me over here? – Savan Gadhiya Apr 23 '20 at 13:05

score 2 · Answer 6 · answered Nov 07 '17 at 08:01

2

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();
    } );

answered Nov 07 '17 at 08:01

Tariq

188
1
15

$('#filterrow > th:nth-child(2) > input') is a selector – Tariq Nov 07 '17 at 08:02

Marinescu Adrian · Answer 7 · 2021-02-19T19:36:08.193

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.

score 0 · Answer 8 · answered Dec 22 '11 at 21:02

0

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

Where filter_value is the string entered in the filter field.

answered Dec 22 '11 at 21:02

matino

17,199
8
49
58

1

Didn't return any results...? – JimmyJammed Dec 22 '11 at 21:51

score -1 · Answer 9 · answered Mar 28 '18 at 23:11

-1

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.

answered Mar 28 '18 at 23:11

Alejandro Marin

47
2

In my case, I am passing multiple values for search and this solution is not working for me. Can you help me over here? – Savan Gadhiya Apr 23 '20 at 13:07
This absolutely does not work. Just as with the default behaviour of search, this will match values in the table containing the search term, not just those exactly matching it. – Philip Stratford Oct 13 '21 at 11:15

score -3 · Answer 10 · answered May 22 '17 at 06:42

-3

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

answered May 22 '17 at 06:42

Benjie T

1

jQuery DataTables - Filter column by exact match

10 Answers10

Linked

Related