Sqlite String Contains Other String Query

SQLite string contains other string query

Using LIKE:

SELECT *
  FROM TABLE
 WHERE column LIKE '%cats%'  --case-insensitive

SQLite Select from where column contains string?

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".

How to check if a string contains a value in the database?

You can use sqlite instr:

instr(X,Y)

The instr(X,Y) function finds the first occurrence of string Y within string X and returns the number of prior characters plus 1, or
0 if Y is nowhere found within X. Or, if X and Y are both BLOBs, then
instr(X,Y) returns one more than the number bytes prior to the first
occurrence of Y, or 0 if Y does not occur anywhere within X. If both
arguments X and Y to instr(X,Y) are non-NULL and are not BLOBs then
both are interpreted as strings. If either X or Y are NULL in
instr(X,Y) then the result is NULL.

Something like:

SELECT * from table
WHERE instr("Let's see the value of John Smith in the database",name) <> 0

You might want to lower() or upper() all strings to handle case.

Android SQLite query to return rows that contains certain text

Since you use ? place holders for the parameters there is no need to concatenate them. You can pass them in the 2nd argument of the rawQuery() method:

String myQuery = "select * from " +  MY_TABLE + " where " + COLUMN1 + " LIKE '%' || ? || '%'";
Cursor c = db.rawQuery(myQuery, new String[] {myString});

where db is an instance of SQLiteDatabase.

The query will return all the rows that contain the value of the string myString in the column COLUMN1.

You can do the same with the function INSTR() instead of the operator LIKE:

String myQuery = "select * from " +  MY_TABLE + " where instr(" + COLUMN1 + ", ?)";

SQLite Select data where the column name contains a string and create a table with those headers only

If you want all the columns of old_table then you can do it like this:

create table new_table as
select * from old_table
where 0;

If you want specific columns you must specify them on the select list:

create table new_table as
select col1, col2, ... from old_table
where 0;

How to create SQLite query to insert a string that contains both and '?

The answer is to let the library do the quoting.

row = """Cristina O'Brien "Valenzuela" """
query = "INSERT INTO Actors (Actor) VALUES (?);"

conn.execute(query, (row,))

Find if String[] contains String in Room database Android

For those who seek for the answer.

The problem was with my Query. I just updated it with this.

@Query("select * from conversations where channelId = :channelId AND status = :status AND" +
            " agentsId LIKE '%' || :agentId || '%' ")

Hope it helps.

How to replace or convert a SQLite column string value with another string value?

There is no need to replace or trim the column values.

If all you need is to query the table then use a CASE expression:

SELECT CASE WHEN columnname LIKE '%COMPLETED%' THEN 'COMPLETED' END AS aliasname,
       <other columns>  
FROM tablename

Replace columnname with th ecolumn's name and aliasname with the alias of the column.

If you want only the rows that contain 'COMPLETED', set the condition in the WHERE clause and return a column with the value 'COMPLETED':

SELECT 'COMPLETED' AS aliasname,
       <other columns>  
FROM tablename
WHERE columnname LIKE '%COMPLETED%'

Firedac Sqlite Query fails when query string contains !

The ! (exclamation mark) character, and several others, require special handling in FireDAC SQL, because they have special meanings in it.

This is fully documented in the "Special Character Processing" section of the Preprocessing Command Text (FireDAC) documentation, see the ResourceOptions MacroCreate, MacroExpand, EscapeExpand properties in that section.

BTW, it's a bad idea to construct SQL by concatenating text which includes user input, or field values derived from it, because it is vulnerable to SQL Injection Attacks.

---

Related Topics