SQLite Forum

sqlite3 LIKE clause with parameters for c api
Login

sqlite3 LIKE clause with parameters for c api

(1) By anonymous on 2021-07-23 20:52:22 [source]

What is the correct syntax for an sql statement that contains % in conjunction with the LIKE operator and can be used for c api functions like sqlite3_prepare_v2() ?
I tried

"SELECT ... FROM ... WHERE col LIKE '%?1%';"
Or
"SELECT ... FROM ... WHERE col LIKE '%%?1%%';"
Or
"SELECT ... FROM ... WHERE col LIKE '%%'+?1+'%%';"
Or
Many others, but nothing worked.

Any suggestion would be very apreciate.

Cristian Danciu

(2.1) By Stephan Beal (stephan) on 2021-07-23 21:29:14 edited from 2.0 in reply to 1 [link] [source]

"SELECT ... FROM ... WHERE col LIKE '%%'+?1+'%%';"

In SQL the string concatenation operator is ||, so:

SELECT ... FROM WHERE col LIKE '%' || ?1 || '%'

Whether or not the % signs need to be doubled depends on the exact API you're passing the string to, but sqlite3_prepare_v2() does not expand/process percent signs so do not double them.

(3) By anonymous on 2021-07-23 21:27:11 in reply to 2.0 [link] [source]

Thank you, Stephan, IT worked great  with one % sign as you mentioned. 
You saved my day (actually night.)
IT is very appreciated. Thanks.