how to write a sql query to search a sub string in column and the sub string is a variable

Question

I have written an sqlite3 query to execute the below,

cur.execute("select * from table where column like '%s'"% (variable1))

which gives the strings the completely match with the variable1. But I want an sqlite3 query that can show:

cur.execute("select * from table where column *contains* '%s'"% (variable1))

What query should I write find any string that contains 'variable1'.
Any help would be appreciable.

Which dbms are you using of those 3? (Don't tag products not involved.) — jarlh, Apr 26 '16 at 07:59
And what is the issue? The second statement is missing the closing double quote, but other than that, why doesn't it work? — nephlm, Apr 26 '16 at 08:06
I am using sqlite3 database. closed the double quotes, still doesnt work. — Nitya, Apr 26 '16 at 09:32

score 0 · Answer 1 · edited Nov 19 '16 at 10:07

0

See, LIKE functionality is simple.

If you want to search a string, you will have to place it in single quotes:

search all names starting with s

select name from names where name like 's%';

search all names ending with s

select name from names where name like '%s';

search all names that contain s

select name from names where name like '%s%';

But if you have stored your string in a variable var

You just have to do this:

select name from names where name like var;

edited Nov 19 '16 at 10:07

marc_s

answered Apr 26 '16 at 08:24

MontyPython

what can i write for select name from names where name like %var. that is all names that ends with the variable. – Nitya Apr 26 '16 at 08:28
@Nitya - select * from train where name like concat('%',@var,'%'); – MontyPython Apr 26 '16 at 08:34
@Nitya - Which database are you using? – MontyPython Apr 26 '16 at 08:58
@Nitya - Try searching Google. :) – MontyPython Apr 26 '16 at 09:05
http://stackoverflow.com/questions/6134415/how-to-concatenate-strings-with-padding-in-sqlite – MontyPython Apr 26 '16 at 09:06

score 0 · Answer 2 · answered Apr 26 '16 at 09:32

0

To ignore characters, you have to put percent signs into the LIKE pattern (and double them so that the % operator does not try to replace them):

cur.execute("select * from table where column like '%%%s%%'" % (variable1))

But direct string interpolation brings formatting problems and possibly SQL injections. Better use a parameter:

cur.execute("select * from table where column like ?", ['%' + variable1 + '%'])

answered Apr 26 '16 at 09:32

CL.

score 0 · Answer 3 · edited May 23 '17 at 10:32

0

Try

cur.execute("""select * from table where column like '%{}%' """.format(variable))

Refer this link for sql

edited May 23 '17 at 10:32

Community

answered Apr 26 '16 at 11:18

Hafees Kazhunkil

3 Answers3