如何为SQL的LIKE语句添加通配符以扩大查询结果范围？

Hey Amanda, let's get this wildcard issue sorted out for you!

First, let's break down why your current setup is only returning exact matches: your SQL snippet uses LIKE '${user.employee.organization.divisionId}' which, when your user's divisionId is ENV/BPOS, translates to rm.dv_id LIKE 'ENV/BPOS'—this acts exactly like an = operator since there are no wildcards.

The problem you ran into when adding % is likely either a template syntax quirk or not targeting the right prefix. Let's cover the two common scenarios you might need:

Scenario 1: Match all records starting with "ENV" (fixed prefix) #

If you always want to pull records where dv_id starts with ENV regardless of the user's divisionId, you can hardcode the prefix with the wildcard:

sql = "(rm.dv_id LIKE 'ENV%')"

This will match ENV/BPOS, ENV/CPD, ENV/XYZ, etc.

Scenario 2: Match records starting with the prefix from the user's divisionId #

If you want to dynamically use the first part of the user's divisionId (e.g., take ENV from ENV/BPOS, XYZ from XYZ/ABC), you'll need to split the divisionId string before adding the wildcard. The exact syntax depends on your template engine, but here's how it might look in common engines:

Example for Freemarker: #

sql = "(rm.dv_id LIKE '${user.employee.organization.divisionId?split('/')[0]}%')"

This splits the divisionId at the / character, takes the first segment (e.g., ENV), then appends % to create a prefix match.

Example for Thymeleaf: #

sql = "(rm.dv_id LIKE '${#strings.substringBefore(user.employee.organization.divisionId, '/')}%')"

Why your initial % addition might have failed: #

• If you added % outside the single quotes, the template engine might have interpreted it as syntax instead of part of the SQL string.
• If you kept the full divisionId (like ENV/BPOS%), that would only match records starting with ENV/BPOS (e.g., ENV/BPOS/123), not all ENV-prefixed entries.

Just make sure the wildcard % is inside the single quotes in your final SQL string—this tells the database to treat it as a wildcard character rather than literal text.

内容的提问来源于stack exchange，提问作者Amanda F