Skip to Content
Technical Articles
Author's profile photo Minjie Lao

How to achieve fuzzy search and case insensitive search in Fiori Smart Table


The blog shows how to achieve fuzzy search and case insensitive by overwriting the CQL (CDS Query Language).

If you are not familiar with CQL, please refer to the link



As a fiori (SAPUI5) developer, most of you knows smart table and smart filter, they provide a rapidly way to customize a inquiry page by using CAP or XML annotations. You can create codeless filter conditions by customizing your CDS file.

But sometimes, those auto-built filter conditions may not fulfill your needs, for example,smart filter only allow user search specific condition with exact case sensitive full string

Alternatively, smart filter provided general search bar, user can achieve fuzzy search or case insensitive search for all of the columns in the smart table.

But seems there is no way to do a fuzzy search or case insensitive search against specific filter.

After some researching and guidance from experts, I found another way, may be make your life easier.



Before discussing the solution, just want to ensure we understand how the fiori send request to database via CDS

Refer to the diagram, basically, fiori view triggers the event function defined in controller.js, then send the request to CDS, and CAP service compile the cds view to a Json format request, then send to database.

As we know, CAP allows cds register API (for more info, please refer to here  ), we can customize an event script before/on/after the cds entity is called.

‘Before event script’ is happened after the json query generated and before sending to database.

So far, I think you may got what we could do in next :), it’s time talk about the solution






First of all, let’s look at what is Json Query looks like, actually, you can easily fetch the query by using this code in your <cds_catelog>.js file



But it returns a very huge json content result. we just need to overwrite the select statement, you can find the where clause is like following, under the json node (req.query.SELECT.where )

{ ref: [ 'COLUMNNAME1' ] }, 
{ val: 'your value1' } ,
{ ref: [ 'COLUMNNAME2' ] }, 
{ val: 'your value2' } ,


what we need to a new statement with “LIKE” operator and “upper case function” like following format.

Please note that here, the sample value (‘your value1’ ‘your value2’) are convert to upper case ensure it can match to the column properly.

And we add the topper function to convert the COLUMN to upper case.

    func: 'toupper',
    args: [ { ref: [ 'COLUMN1' ] } ]
  { val: '%YOUR VALUE1%' },


    func: 'toupper',
    args: [ { ref: [ 'COLUMN2' ] } ]
  { val: '%YOUR VALUE2%' },




After you understood the way, we can easily overwrite the json query under ‘Before Event’ API


This is my the sample code in registered API

Please note, I used ‘*‘ (asterisk) to allow this query conversion can be triggered before any of entity under the catalog, if you just want some of entity use the conversion script, then put your entity name.

I put some logic to avoid some condition is convert incorrectly.

this.before ('READ', '*' ,async (req)=>{


        console.log("before searching");


        let conditions =req.query.SELECT.where;

        //req.query.SELECT.where[1] = 'LIKE';
        //req.query.SELECT.where[2].val = `%${conditions[2].val}%`;

        //req.query.SELECT.where.push({ func: 'contains', args: [ { ref: [ 'KEYWORD' ] }, { val: '20' } ] });

        var newCondition = [];

        if (conditions){

            conditions.forEach((condition,index) => {
                var scol ='';
                var sval = '';
                var sOperator = '';
                if(condition==='and' || condition==='or'||condition==='('||condition===')'
                || condition.func !=undefined
                    //newCondition.push({ func: 'contains', args: [ { ref: [ scol ] }, { val: sval } ] });

                        newCondition.push({ func:'toupper', args:[{ref: [ scol ] }]},'like',{ val: `%${sval.toUpperCase()}%` });
                        newCondition.push({ ref: [ scol ] },sOperator,{ val: sval });
                    //newCondition.push(`${scol} like '%${sval}%'`);

        //overwrite the condition
        //req.query.SELECT.where = newCondition;

        if(newCondition&& newCondition.length>0){
            req.query.SELECT.where = newCondition;


From the blog, we learnt:
  • 1. CAP Service generated CQL is editable
  • 2. CQL is not completely same as SQL
  • 3. How to overwrite the Json request.


The CAP service API dose not only offer the way of pre-processing or post-processing of calling odata entity from data utilization perspective, but also showing a way of customizing the system generated request.


Let me know if any question.

Hope this article can help you.


Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Yogananda Muthaiah
      Yogananda Muthaiah

      Great Article Minjie Lao !!

      Author's profile photo Minjie Lao
      Minjie Lao
      Blog Post Author

      thanks yoga.

      Author's profile photo Gregor Wolf
      Gregor Wolf

      Please also add the tag SAP Cloud Application Programming Model so it could be found better.

      Would be great if you could enhance your example to use the built in HANA Cloud FUZZY Search functionality.

      Author's profile photo Minjie Lao
      Minjie Lao
      Blog Post Author

      Hi Gregor,

      Thanks for the suggestion, tag is added,


      In terms of the HANA Cloud FUZZY Search functionality,  I will take a look and then refine the article