CODEWITHSUNDEEP
jqueryregexsearchfilterdatatables
JimmyJammed
JimmyJammed

Reputation: 9698

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?

Upvotes: 33

Views: 58985

Answers (10)

Marinescu Adrian
Marinescu Adrian

Reputation: 1

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.

Upvotes: 0

anilam
anilam

Reputation: 892

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

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

Upvotes: 3

Wolverine
Wolverine

Reputation: 1702

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!

Upvotes: 4

Alejandro Marin
Alejandro Marin

Reputation: 47

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.

Upvotes: -1

Tariq
Tariq

Reputation: 188

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

Upvotes: 2

Benjie T
Benjie T

Reputation: 1

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

Upvotes: -3

Keith.Abramo
Keith.Abramo

Reputation: 6965

$(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.

Upvotes: 11

Neeno Xavier
Neeno Xavier

Reputation: 571

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 )

Upvotes: 55

JimmyJammed
JimmyJammed

Reputation: 9698

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

Upvotes: 26

matino
matino

Reputation: 17725

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

Where filter_value is the string entered in the filter field.

Upvotes: 0

Related Questions