Using the '?' Parameter in SQL LIKE Statement

Question

I'm accessing a Firebird database through Microsoft Query in Excel.

I have a parameter field in Excel that contains a 4 digit number. One of my DB tables has a column (TP.PHASE_CODE) containing a 9 digit phase code, and I need to return any of those 9 digit codes that start with the 4 digit code specified as a parameter.

For example, if my parameter field contains '8000', I need to find and return any phase code in the other table/column that is LIKE '8000%'.

I am wondering how to accomplish this in SQL since it doesn't seem like the '?' representing the parameter can be included in a LIKE statement. (If I write in the 4 digits, the query works fine, but it won't let me use a parameter there.)

The problematic statements is this one: TP.PHASE_CODE like '?%'

Here is my full code:

SELECT C.COSTS_ID, C.AREA_ID, S.SUB_NUMBER, S.SUB_NAME, TP.PHASE_CODE, TP.PHASE_DESC,       TI.ITEM_NUMBER, TI.ITEM_DESC,TI.ORDER_UNIT,
C.UNIT_COST, TI.TLPE_ITEMS_ID FROM TLPE_ITEMS TI
INNER JOIN TLPE_PHASES TP ON TI.TLPE_PHASES_ID = TP.TLPE_PHASES_ID
LEFT OUTER JOIN COSTS C ON C.TLPE_ITEMS_ID = TI.TLPE_ITEMS_ID
LEFT OUTER JOIN AREA A ON C.AREA_ID = A.AREA_ID
LEFT OUTER JOIN SUPPLIER S ON C.SUB_NUMBER = S.SUB_NUMBER

WHERE (C.AREA_ID = 1 OR C.AREA_ID = ?) and S.SUB_NUMBER = ? and TI.ITEM_NUMBER = ? and **TP.PHASE_CODE like '?%'**

ORDER BY TP.PHASE_CODE

Any ideas on alternate ways of accomplishing this query?

Answer 1

If you use `LIKE '?%', then the question mark is literal text, not a parameter placeholder.

You can use LIKE ? || '%', or alternatively if your parameter itself never contains a LIKE-pattern: STARTING WITH ? which might be more efficient if the field you're querying is indexed.

Answer 2

You can do

and TP.PHASE_CODE like ?

but when you pass your parameter 8000 to the SQL, you have to add the % behind it, so in this case, you would pass "8000%" to the SQL.