Concatenation of String and params in mybatis

I have searched for this and + and $ won't work. I wan't to concatenate % with #search param in mybatis select query. I want to achieve this in mybatis XML config not service(Java)...

I have this query

<select id="carriersDataTable" parameterType="map" resultType = "carrier">
        SELECT 
        carrierId
        name, 
        prefix,
        country, 
        status,
        isdirect as direct
        FROM carriers
        WHERE name LIKE '%' + #{search} + '%' AND Status != -1 AND IF(''=#{status}, 1, Status = #{status})
        ORDER BY name
        LIMIT #{startIndex}, #{fetchSize}
    </select>

I have tried LIKE '%' + #{search} + '%' and LIKE ${%#{search}%} both won't work

Solution 1:

Try the built-in string concatenation function in the DB, e.g.

LIKE CONCAT('%', #{search}, '%')

For MySQL

Solution 2:

Try this '%' || #{search} || '%' It will solve your problem.

Solution 3:

if you want to keep the % in sql you must use the $ symbol

'%' + '${search}' + '%'

keep in mind thant using ${} expose your query to sql injection so you must escape the search string properly in java code