Using "like" wildcard in prepared statement

sql

prompt-engineering

best-practices

performance

byNikita Barsukov·Dec 18, 2024

Here's the quick takeaway: Incorporate the % wildcard into the search term parameter to utilize it within a PreparedStatement and a LIKE clause:

PreparedStatement pstmt = connection.prepareStatement("SELECT * FROM table WHERE column LIKE ?");
pstmt.setString(1, "term%");
ResultSet rs = pstmt.executeQuery();

The above fetches rows where column begins with "term". To discover "term" at any position within column, modify the parameter to %term%.

Deploying wildcards effectively

Crafting varying search patterns with wildcards

In SQL's LIKE clause, % is a wildcard that marries any sequence of characters. Place it intelligently in your PreparedStatement for different search types:

Starts with: Use "term%" to snare strings beginning with "term".
Ends with: Set your trap with "%" + term to nab strings ending with "term".
Contains: Implement "%" + term + "%" to net "term" anywhere in the string.

Dodging special characters' mischief

Pesky special characters could wreak havoc with wildcard functionality. Nip the chaos in the bud — escape these characters using the ESCAPE clause:

PreparedStatement pstmt = connection.prepareStatement("SELECT * FROM table WHERE column LIKE ? ESCAPE '!'");

String searchTerm = "10%!"; // Hold up! We got special characters here
String escapedTerm = searchTerm.replace("!", "!!").replace("%", "!%");
pstmt.setString(1, "%" + escapedTerm + "%");
ResultSet rs = pstmt.executeQuery();

In the code, ! is the escape character, neutralizing any special characters' "special" effects.

CONCAT: Wildcards' best buddy

If your database is on talking terms with the CONCAT function, use it to add wildcards into your SQL statement dynamically:

PreparedStatement pstmt = connection.prepareStatement("SELECT * FROM table WHERE column LIKE CONCAT('%', ?, '%')");
pstmt.setString(1, term); // 'term' is the search string you're after
ResultSet rs = pstmt.executeQuery();

(Note: Replace ? with your term when in CONCAT, not with '%your_term%'. It's not Inception here!)

Getting ahead with additional insights

The not-so-obvious trade-off: Performance with LIKE

While working your query magic with LIKE, remember:

Leading wildcards (i.e., "%term") could slow down your search due to inefficient queries as they prevent your indexes from working.
As much as possible, add a wildcard at the end of the term (i.e., "term%"). It plays nice with indexes when they're around.

Wildcard strings: Set them free (safely)!

Seeing \u0025, which denotes %, when setting wildcard strings might bewilder you. In reality, setting your parameter value directly with a % symbol is usually your best bet:

pstmt.setString(1, "%" + term + "%");
// Smells like efficiency! And good code hygiene.

Position your wildcard just right

Your wildcard's position within the search term can be a gamechanger. Check out these examples:

Starts with term: Find terms where 'pattern' is followed by anything: "pattern%".
Ends with term: To match any strings that end in 'pattern': "%" + pattern.
Contains term: Spot terms where 'pattern' is surrounded by any characters: "%pattern%".