- Wild Card (%) & LIKE in SQL

using LIKE in SQL

Adding a WildCard (%) search to an Operation using LIKE:

Within WOW you can create searches or prompts in many different ways. You can search with Operators such as LIKE, =, <, > and helpers like a Date Picker. There are times when you would like to search through a column for certain text. It may be a search for keywords or documentation. The LIKE condition is used to specify a search for a pattern in a column.

Here is an example of a basic SQL Operation with a search using the LIKE operator. For example:

SELECT * FROM samples.notes WHERE notes LIKE ?

If a user enters "Jon" then all records which have a notes field that starts with "Jon" will be returned. WOW does this by substituting the "Jon" with "Jon%" and then executes the SQL statement. A "%" sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern.

Note: Whenever using the LIKE operator, WOW will always add the "%" operator after the search parameter.

There are cases where you want to search through an entire text field or area and return the pattern matches. You still would like the user to enter the search string but want to search for that pattern throughout the entire column and even parts of words within entire column. To do this we need to add the wildcard before our search parameter by putting together “%” and “?” (Use CONCAT function or similar SQL function).

SELECT * FROM samples.notes WHERE notes LIKE CONCAT (‘%’ , ?)

NOTE: CONCAT function may require casting on certain Databases such as DB2/400:

(SELECT * FROM samples.notes WHERE notes LIKE CONCAT (‘%’ , CAST( ? as CHAR(10))

In this case if a user enters “Jon”, WOW will replace it with “%Jon%” and return any record that has “Jon” anywhere within the column.

Note: Also look at how you can set a default value as a wildcard by clicking here.

Using WildCard %

Wild Card % Prepended Field property

If you want to search for a String value ANYWHERE in text without requiring the end user to start their search with a %, the following solution can be used.

This is a prebuilt java class that will prepend '%' in front of it's value PRIOR TO THE DATABASE search to enable wild card searches for data types of char.

In a field descriptor manager, you can set the Advanced Setting property of Field Class to Wild Card % Prepended Field to any field you want to insert a wildcard (%) in front of when using that field in an SQL search query. This takes away the need to use the CONCAT() function in SQL.

For More Information on SQL LIKE, and Wildcard (%) operator check out:

• http://www.w3schools.com/sql/sql_like.asp - using LIKE

• http://w3schools.com/sql/sql_wildcards.asp - using WildCard (%)