{"id":3549,"date":"2016-06-14T03:00:22","date_gmt":"2016-06-13T19:00:22","guid":{"rendered":"http:\/\/www.finereport.com\/en\/?p=3549"},"modified":"2019-10-14T16:18:11","modified_gmt":"2019-10-14T08:18:11","slug":"fuzzy-query","status":"publish","type":"post","link":"https:\/\/frg.fineres.com\/en\/2016\/06\/14\/fuzzy-query\/","title":{"rendered":"Description of Fuzzy Query."},"content":{"rendered":"

Fuzzy query is a simple method to query data meeting certain formats in report designer<\/a>. So let’s have a look!<\/p>\n

1. Problem description<\/h3>\n

Fuzzy query in report designer is a simple method to query data meeting certain formats. For example, if you want to query all data containing character \u201ca\u201d, you can use fuzzy query.<\/p>\n

2. Fuzzy query syntax<\/h3>\n

In fuzzy query of Finereport BI<\/span><\/a>, \u201c_\u201d represents a single character and \u201c%\u201d represents any character for matching. Some common formats are as follows:<\/p>\n

Select * from Table Name where Column Name like ‘%’; \/\/ Query All<\/p>\n

Select * from Table Name where Column Name like ‘x’; \/\/ Complete Matching Query<\/p>\n

Select * from Table Name where Column Name like ‘_x’; \/\/if right is x, there is one character in the front<\/p>\n

Select * from Table Name where Column Name like ‘__x’; \/\/if right is x, there are two bytes of characters in the front<\/p>\n

Select * from Table Name where Column Name like ‘x__’; \/\/if left is x, there are two bytes of characters in the back<\/p>\n

Select * from Table Name where Column Name like ‘%x’; \/\/if right is x, there are any bytes of characters in the front<\/p>\n

Select * from Table Name where Column Name like ‘x%’; \/\/if left is x, there are any bytes of characters in the back<\/p>\n

Select * from Table Name where Column Name like ‘%x%’; \/\/if middle is x, there are any bytes of characters in middle<\/p>\n

Combine parameter fuzzy query (replace the above x with ${name}):<\/p>\n

Select * from Table Name where Column Name like ‘${name}’;<\/p>\n

Select * from Table Name where Column Name like ‘%${name}’;<\/p>\n

And so on.<\/p>\n

3. Example<\/h3>\n

Use fuzzy query to realize that \u201cif parameter is Null, select All\u201d<\/p>\n

Create a new workbook and add data set ds1 with sql statement: SELECT*FROM Sales Volume WHERE Area like ‘%${area}’<\/strong><\/span><\/p>\n

Effect viewing<\/strong><\/p>\n

Click Preview button on the Data Set Panel to pop up Parameter dialog. For example, if \u201cEast China\u201d is input, the system only displays product sales volume in East China:<\/p>\n

\"\"<\/p>\n

If no parameter value is input, the system will display product sales volumes of all areas:<\/p>\n

\"\"<\/p>\n","protected":false},"excerpt":{"rendered":"

Fuzzy query is a simple method to query data meeting ce…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[111],"tags":[117],"_links":{"self":[{"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/posts\/3549"}],"collection":[{"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/comments?post=3549"}],"version-history":[{"count":6,"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/posts\/3549\/revisions"}],"predecessor-version":[{"id":8257,"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/posts\/3549\/revisions\/8257"}],"wp:attachment":[{"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/media?parent=3549"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/categories?post=3549"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/tags?post=3549"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}