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.
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.
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>
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%'