jQuery Datatables - SQL NOT LIKE Output

BuggedSoul

I am using jQuery Datatables to filter the table contents using inputbox.

I want the output similar to SQL QUERY SELECT * from <Table-Name> where Description Not Like '%(SC)%'

Basically, I would like to display all the contents which do not have "(SC)" in their description along with the input provided in input textbox '#myInputTextField'.

I managed to get fetch the results which display the contents with (SC) in their description.

 $(document).ready(function () {
        $('#myTable').DataTable({
            "ajax": {
                "url": "/home/loaddata",
                "type": "GET",
                "datatype": "json"
            },
            "columns": [
                    { "data": "IdNo", "autoWidth": true },
                    { "data": "Description", "autoWidth": true },
            ]

        });
        check();
    });

    //Radio Toggle

    function check(radiocheck) {

        if (radiocheck == 1) {
            oTable = $('#myTable').DataTable();
            $('#myInputTextField').keyup(function () {
                var key=$(this).val();
                oTable.search('^(?:(?!(SC)).)*$', true, false).draw();  <---- This is where I want to show the data without "(SC)" matching the text entered into the textbox.
            })

        }
        else if (radiocheck == 2) {
            oTable = $('#myTable').DataTable();
            $('#myInputTextField').keyup(function () {
                oTable.search($(this).val() + " (SC)").draw();
            })

        }
    }

Thanks

Response To Allan:

Hello Allan,

Thanks for the quick answer.

Somewhat yes, The example shows all the contents which do not have Edinburgh in them. I would like to filter those outputs further.

Example: "Software Engineer" from the entries obtained after filtering for "Edinburgh"

So it filters all the entries which do not have Edinburgh. I would like to further filter those entries by entering the keywords in the textbox.

allan

This little example filters out all rows which have "Edinburgh" in them.

Can you modify that example to suit your needs, or to show the issue?

Allan

BuggedSoul

Hello Allan,

Thanks for the quick answer.

Somewhat yes, The example shows all the contents which do not have Edinburgh in them. I would like to filter those outputs further.

Example: "Software Engineer" from the entries obtained after filtering for "Edinburgh"

So it filters all the entries which do not have Edinburgh. I would like to further filter those entries by entering the keywords in the textbox.

allan

You'll probably need to use a custom search plug-in for that. Creating a regex for what you describe might be possible, but its beyond my regex knowledge I'm afraid!

Allan

BuggedSoul

Hello Allan,

How can I implement it using the custom search?

I would like to display all the contents without "Edinburgh" in it and further filter those outputs but searching and matching the keyword entered via textbox.

BuggedSoul

Hello Allan,

Thanks a million.

I am a beginner when it comes to Web development. I am converting my C# winforms app into a web app.

Considering the custom search example. I would like to show details of all the employees except whose office is in "Edinburgh".

Filter rest of the employees via textbox.

So basically sql equivalent of

select * from employees where office not like '%Edinburgh%' ;

This would display all the employees and their details except those whose office is in Edinburgh.
I would like to perform filter on these employees.

Regards

kthorngren

You might be able to do this with the addition of YADCF:
https://github.com/vedmack/yadcf

Take a look at this example:
https://jsfiddle.net/m26jzxsf/6/

The example has the Datatables search input plus the ASSIGNED button and column search under Status. The last two are provided by yadcf. This part is probably more than you need but the table is setup where the Status column has either assigned or notassigned setup as part of the search filter based on the color of the image. Anyway in your case that would probably be a text field and not need this initial setup.

I point this out because you can hit the ASSIGNED button to search for assigned and you will get 3 results. Then you can perform an additional search in the DT search field (2 for example) and display only the PR00002 project. The filter_match_mode has a regex option I think you can use your regex to filter what you want then use normal searches for the rest.

HTH,
Kevin

BuggedSoul

Hello kthorngren,

Considering the following fiddle: https://jsfiddle.net/53futc2w/5/

On pressing the NonSC button, I would like to display the contents which do not have "SC" in the Description. On entering the text into the textbox it should filter from the displayed NON SC values..

kthorngren

How about this:
https://jsfiddle.net/53futc2w/7/

Kevin

BuggedSoul

Thanks a lot Allan and kthorngren,

I was able to successfully implement what I was after.

You can find the Fiddle: https://jsfiddle.net/perfectnoob/1xh3kys5/2/

I have a reset button which unchecks the radio buttons. I would like to clear the filter textbox value on clicking reset.

I am unable to do that with $("#example_filter").val("");.

kthorngren

Looks great!

I tried $("input[type=search]").attr('value', ''); and $("input[type=search]").val(''); but neither cleared the search testbox.

Kevin