23

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+'%']);

?

Blorgbeard
  • 101,031
  • 48
  • 228
  • 272
  • 2
    Note that you should better use parameters for your query, or at least handle quotes within your SearchTerm (e.g. via a QuotedStr() call). – Arnaud Bouchez Sep 06 '12 at 09:29

4 Answers4

35

Use another % in the format string:

SQL.Text := Format('select foo from bar where baz like ''%s%%''',[SearchTerm]);
Robert Gamble
  • 106,424
  • 25
  • 145
  • 137
8

%% , IIRC.

moobaa
  • 4,482
  • 1
  • 29
  • 31
4

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.

Ondrej Kelle
  • 36,941
  • 2
  • 65
  • 128
  • 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. – Blorgbeard Nov 06 '08 at 19:52
  • 1
    +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. – Arnaud Bouchez Sep 06 '12 at 09:31
  • One of my favourite, +1 for that comic only ;-) – Sнаđошƒаӽ Jan 31 '17 at 06:37
1

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%'
Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219
Charmy Vora
  • 51
  • 6