I. Overview
1)Application Scenarios
Fuzzy query is a very convenient way to query data that conforms to a specified format. For example, if a user wants to query all data containing the A character, fuzzy queries can be used.
2)Implementation Roadmap
In FR designer, fuzzy query can be realized by using SQL keywords plus wildcard "_", "%" and other combinations, with parameter widgets for query.
II. Example
1. Creating a template
Create a regular report with the store as: %FR_HOME%\webroot\WEB-INF\reportlets\doc-EN\Parameter\Fuzzy_query_example.cpt
1) Creating a dataset
ds1: SELECT * FROM Sales_Volume where Salesperson like '%${name}%'
As shown below:
2) Report design
Drag the dataset field into the cell and the report style looks like the following:
2. Setting parameter pane
Edit the parameter pane to add three widgets. As shown below:
Add label widget, widget value is Fuzzy query salesperson:.
Add text widget named name, the same as the name in the dataset ${}.
Add Query Button
3. Effect preview
1) PC
Save the template, click Pagination Preview, enter the string of fuzzy query in the text box, and click Query button to query the corresponding sales data, as shown below:
2) Mobile
App and HTML5 end effect preview, as shown below:
3) Completed template
For the completed template, see: %FR_HOME%\webroot\WEB-INF\reportlets\doc-EN\Parameter\Fuzzy_query_example.cpt
Click to download the template:
III. Database usage
This paper will briefly introduce the use of fuzzy query in some databases. You can connect to different databases based on site requirements. For details, see Configure the External Database
| symbol | define | database | sentense |
|---|---|---|---|
| "%" | Represents any zero or more characters | SQLServer Oracle MySQL | Select * from table name where column name like '%' //Query all Select * from table name where column name like 'x' // Perfect match query Select * from table name where column name like '%x' //The right is x, which can be preceded by any bit character Select * from table name where column name like 'x%' //The left is x, which can be followed by any bit character Select * from table name where column name like '%x%' //The middle is x, and the left and right sides can have any bit characters Note: x can be replaced with a parameter，for example: SELECT * FROM Sales_Volume where Salespersom like '%${name}%' |
| "_" | Represents a single character | SQLServer Oracle MySQL | Select * from table name where column name like '_x' //X on the right, preceded by one character Select * from table name where column name like '__x' //X on the right, preceded by a two-bit character Select * from table name where column name like 'x__' //The left is x followed by two bit characters |
| "[]" | Represents one of the characters listed in parentheses.Specifies a character, string, or range that requires the object to be matched to any of them Note: If [] contains a series of characters (01234, abcde, etc.), it can be abbreviated as "0-4", "a-e" | SQLServer | Select * from table name where column name like '[ZhangLiWang]San' //Will find "Zhang San", "Li San", "Wang San" (instead of "Zhang Li Wang San") Select * from table name where column name like 'old[1-9]' //将找出“old1”,“old2”,……,“o9” |
| Oracle | select * from table name where regexp_like(列名,'[ZhangLiWang]San') //Will find "Zhang San", "Li San", "Wang San" (instead of "Zhang Li Wang San") | ||
| MySQL | Select * from table name where column name rlike '[ZhangLiWang]San'//Will find "Zhang San", "Li San", "Wang San" (instead of "Zhang Li Wang San") Select * from table name where column name regexp '[ZhangLiWang]San' //Will find "Zhang San", "Li San", "Wang San" (instead of "Zhang Li Wang San") | ||
| "[^]" | Represents a single character that is not in the parenthesis column The value is the same as [], but it requires any character other than the specified character to be matched | SQLServer | Select * from table name where column name like '[^ZhangLiWang]San'//Will find "Zhao San", "Sun San", etc., not surname "Zhang", "Li", "Wang" Select * from table name where column name like 'old[^1-4]' //Will exclude "old 1" to "old 4", look for "old 5", "old 6",... |
| Oracle | Select * from table name where regexp_like(column name,'[^LiWang]San') //Will find cho San and Sun San who are not named Li or Wang | ||
| MySQL | Select * from table name where column name rlike '[^LiWang]San' //Will find cho San and Sun San who are not named Li or Wang Select * from table name where column name regexp '[^LiWang]San' //Will find cho San and Sun San who are not named Li or Wang | ||
| Special character fuzzy query | When the string to be queried contains wildcards, you can use the following method to query | SQLServer | Use [] to include special characters: Select * from table name where column name like '_[%]_' |
| Oracle | To escape a special character with the escape keyword: Select * from table name where column name like '_\%_' escape '\' | ||
| MySQL | Escape special characters with \ : Select * from table name where column name like '_\%_' |
Note: Fuzzy query in Access is '?','*' ，for example：Select * from table name where column name like '*b*'
IV. Index
| Reference documentation | Application scenarios | Rendering |
|---|---|---|
| Text box to achieve range query | Users can query all data when entering null value, query corresponding data when entering single value, and query data within the range when entering range (separate two numbers with any character). | |
| Text widgert fuzzy query keyword highlighting | When you perform fuzzy query on a report, the fuzzy keywords are highlighted in the data columns that you want to query. | |
| Fuzzy search of drop-down box/drop-down check box/drop-down tree | When using the widget to fill in or query data, if there is a lot of data in the data dictionary, it can be very troublesome to find. Therefore, FineReport provides the fuzzy search function of the widget to improve the input efficiency. | -- |
| Drop - down - box fuzzy search for exact matching scheme | The drop-down box widget has its own fuzzy query function, when matching will be separated by multiple characters to match. In practice, we hope to achieve more accurate fuzzy search | |
| Dropdown box fuzzy search for slow solutions | Through the widget data dictionary linkage method and then using the drop-down box fuzzy search, so as to improve the speed. | |
| JS to achieve drop-down box custom fuzzy search | In the drop-down box query, the fuzzy search of the drop-down box can be detected even if the text is not continuous. It is hoped that the fuzzy search can only match the continuous content. | |
| Infinite interval parameter query | The query panel can be used by comparing symbols such as greater than sign >; Or less than sign < To define an infinite interval query | |
| Drop - down check box multi-value fuzzy query | You want to use the drop-down check box to select multiple values and blur the query by each value | |
| Start time and end time | Query the data within a specified period of time | |
| Weekly report | When a certain date is entered, the data of the current week is queried, that is, weekly report query | |
| Quarterly report | When you enter a date, the data for the quarter of the date is displayed |
Previous：When Value of Drop-down Box is None, Show All Data
