CODEWITHSUNDEEP
sqlite
ThatGuy343
ThatGuy343

Reputation: 2424

SQLite Select from where column contains string?

What I currently have selects based on a column having the same value..

"SELECT * FROM users WHERE uuid = ?"

But what if I want to return a row based on one of the columns "containing" a string value? Some pseudo code would be:

SELECT * FROM users
WHERE column CONTAINS mystring

Any help is appreciated, I have been searching for other answers but to no avail.

Upvotes: 32

Views: 62907

Answers (5)

Matthew John Smith
Matthew John Smith

Reputation: 11

I recently came across this problem and solved it such you can find the string 'time'

text = "SELECT * FROM database WHERE column=" + "'" + str(time) + "'"

cursor.execute(text)

The issue I found was that when you pass in the string time directly as: ..WHERE column=" + time) it formats 'time' as "time" when you want it to format as 'time' which must be to do with the way SQLite is wrote to handle the arguments its passed.

I have time stamped all entries into a database and now I can recall any data from a specific time.

Upvotes: 1

Saleh Hosseini
Saleh Hosseini

Reputation: 523

Checking variable substring ( a more generic answer )

you should use '%'||?||'%' instead

for example in python we'll have something like this:

curser.execute("SELECT * FROM users WHERE column LIKE '%'||?||'%'", (variable,) )

Upvotes: 9

CoDe
CoDe

Reputation: 11146

Just another way using instr, do not need to supply additional character.

Select * from repos where instr("column_name", "Search_string") > 1

Upvotes: 5

John3136
John3136

Reputation: 29266

SELECT * FROM users WHERE column LIKE '%mystring%' will do it.

LIKE means we're not doing an exact match (column = value), but doing some more fuzzy matching. "%" is a wildcard character - it matches 0 or more characters, so this is saying "all rows where the column has 0 or more chars followed by "mystring" followed by 0 or more chars".

Upvotes: 63

Semicolon
Semicolon

Reputation: 1925

Use LIKE clause. E.g. if your string contains "pineapple123", your query would be:

SELECT * from users WHERE column LIKE 'pineapple%';

And if your string always starts with any number and ends with any number like "345pineapple4565", you can use: 

SELECT * from users WHERE column LIKE "%pineapple%";

Upvotes: 12

Related Questions