In Delphi 7, how do I escape a percent sign (%) in the Format function?

Question

I want to do something like this:

SQL.Text := Format('select foo from bar where baz like ''%s%''',[SearchTerm]);

But Format doesn't like that last '%', of course. So how can I escape it? \%? %%?

Or do I have to do this:

SQL.Text := Format('select foo from bar where baz like ''%s''',[SearchTerm+'%']);

?

Kempis

Note that you should better use parameters for your query, or at least handle quotes within your SearchTerm (e.g. via a QuotedStr() call). – Roxannaroxanne

Answer 1

Use another % in the format string:

SQL.Text := Format('select foo from bar where baz like ''%s%%''',[SearchTerm]);

Pediculosis

Answer 2

%% , IIRC.

Incriminate

Answer 3

Obligatory: http://xkcd.com/327/ :-)

Depending on context, your approach might be vulnerable to SQL injection. If the search term comes from user input it would probably be better to use a parameterized query or at least try to sanitize the input.

Diffractometer

True, it would be vulnerable.. but this is for a throwaway POC. I haven't actually figured out how to use parameterized queries with LIKE. – Kempis

+1 Parameters will be more secure, and somewhat faster (depending on your DB engine, and if you re-use a prepared statement). You replace the string value by a parameter, and won't touch the LIKE expression. So you can write SQL.Text := 'select foo from bar where baz like :TERM' and ParamByName('TERM').AsText := SearchTerm+'%'. No more problem with quotes and % chars. – Roxannaroxanne

One of my favourite, +1 for that comic only ;-) – Blanchard

Answer 4

Add 2 percent sign to have 1 single %
Example :

 Format('select foo from bar where baz like ''%%%s%%'',[SearchString])

Gives you

select foo from bar where baz like '%SearchString%'

Eyler