CODEWITHSUNDEEP
sqlitesearch
Mobile Developer iOS Android
Mobile Developer iOS Android

Reputation: 6016

Multi column search in SQLITE

I have to search a single string in multiple coloumn in SQLITE but I am unable to get the results, I am using the following query to do the search:

Select * 
From MyTable 
Where (col1 || '--' || col2) like '%abc xyz 123%'

Let's say I have 'abc' in col1 and 'xyz 123' in col2.

Any help.

Upvotes: 0

Views: 110

Answers (3)

Mobile Developer iOS Android
Mobile Developer iOS Android

Reputation: 6016

Correct answer is that I have to modify the above query a little bit like this,

Select * From MyTable Where (col1 || ' ' || col2) like '%abc xyz 123%'

Upvotes: 0

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11151

Lets say I have 'abc' in col1 and 'xyz 123' in col2.

col1 || '--' || col2 is equal 'abc--xyz 123', so will not match '%abc xyz 123%'!

You should use

 SELECT * FROM MyTable WHERE col1 || ' ' || col2 LIKE '%abc xyz 123%'

or

 SELECT * FROM MyTable WHERE col1 || '--' || col2 LIKE '%abc--xyz 123%'

Note: parenthesis are not needed as || has higher precedence than LIKE.

Upvotes: 1

PositiveLogic
PositiveLogic

Reputation: 316

The operands of your LIKE statement have to match criteria. If you are looking for a "single string" exact match from concatenation of the fields in those two columns and the (col1) field has 'abc' while the (col2) field has 'xyz 123' ...then the left-hand operand of your LIKE is the concatenation (col1 || col2) and the right-hand operand is the concatenation ('%abc%' || '%xyz 123%') resulting in the statement:

SELECT * FROM MyTable WHERE (col1 || col2) LIKE '%abc%'||'%xyz 123%';

or

SELECT * FROM MyTable WHERE (col1 || '--' || col2) LIKE '%abc%'||'--'||'%xyz 123%';

Example:

C:\SQLite3>sqlite3 test
    SQLite version 3.8.1 2013-10-17 12:57:35
    Enter ".help" for instructions
    Enter SQL statements terminated with a ";"
    sqlite> CREATE table MyTable (col1 text, col2 text);
    sqlite> INSERT into MyTable (col1, col2) values ('abc', 'xyz 123');
    sqlite> SELECT * FROM MyTable WHERE (col1 || col2) LIKE '%abc xyz 123%';
    sqlite> SELECT * FROM MyTable WHERE (col1 || col2) LIKE '%abc%' || 'xyz 123%';
    abc|xyz 123
    sqlite> SELECT * FROM MyTable WHERE (col1 ||'--'|| col2) LIKE '%abc xyz 123%';
    sqlite> SELECT * FROM MyTable WHERE (col1 ||'--'|| col2) LIKE '%abc%'||'--'||'%xyz 123%';
    abc|xyz 123
    sqlite>

Upvotes: 0

Related Questions