CODEWITHSUNDEEP
androidsqlsqliteandroid-sqlite
OFR
OFR

Reputation: 90

SQLite query to fetch rows containing words from given sentence only?

I have a column containing text strings, and I want to fetch all rows which contain words from given sentence, for example:

if given sentence:

the red apple on the table in the middle of the field

and column contains following rows:

  1. the red
  2. red apple of the field
  3. green apple
  4. red apple on the chair
  5. table

I want my query to return row number 1, 2 and 5 only.

Upvotes: 0

Views: 757

Answers (3)

seema
seema

Reputation: 991

Basically you need to split your row text on basis of white spaces. Now check "Like %+splitString[i]+%" for each slpit string and then apply AND operation on that.

Upvotes: 1

OFR
OFR

Reputation: 90

For the example above, this query worked for me:

SELECT * FROM [table] WHERE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([column],'the',''),'red',''),'apple',''),'on',''),'the',''),'table',''),'in',''),'the',''),'middle',''),'of',''),'the',''),'field',''),' ','') = ''

This is the java code to generate the SQL query automatically for input string "speechString":

String[] words = speechString.split("\\s+");
for (int i = 0; i < words.length; i++) {
    words[i] = words[i].replaceAll("[^\\w]", "");
}
String replaceStr = "REPLACE("+[column]+",'"+words[0]+"','')";
for (int i = 1; i < words.length; i++) {
    replaceStr = "REPLACE("+replaceStr+",'"+words[i]+"','')";
}
replaceStr = "REPLACE("+replaceStr+",' ','')";
String query = "SELECT * FROM "+[table]+" WHERE "+replaceStr+" = ''";

Replace [column] and [table] in the above code.

Upvotes: 0

Richard Hansell
Richard Hansell

Reputation: 5403

This should do it:

SELECT [RowNumber] FROM [Table] WHERE INSTR('the red apple on the table in the middle of the field', [ColumnText]) <> 0

Obviously you will need to change the bits in square brackets to match your database.

Edit - right, my fault - I never read your question fully to realise you also want to find cases where the words match in the right order but the entire phrase doesn't. I can do this in TSQL like this:

DECLARE @Sentence VARCHAR(100) = 'the red apple on the table in the middle of the field';
DECLARE @Column VARCHAR(100) = 'the red';
SELECT @Column, '%' + REPLACE(@Column, ' ', '%') + '%', CASE WHEN @Sentence LIKE '%' + REPLACE(@Column, ' ', '%') + '%' THEN 1 ELSE 0 END;
SELECT @Column = 'red apple of the field';
SELECT @Column, '%' + REPLACE(@Column, ' ', '%') + '%', CASE WHEN @Sentence LIKE '%' + REPLACE(@Column, ' ', '%') + '%' THEN 1 ELSE 0 END;
SELECT @Column = 'green apple';
SELECT @Column, '%' + REPLACE(@Column, ' ', '%') + '%', CASE WHEN @Sentence LIKE '%' + REPLACE(@Column, ' ', '%') + '%' THEN 1 ELSE 0 END;
SELECT @Column = 'red apple on the chair';
SELECT @Column, '%' + REPLACE(@Column, ' ', '%') + '%', CASE WHEN @Sentence LIKE '%' + REPLACE(@Column, ' ', '%') + '%' THEN 1 ELSE 0 END;
SELECT @Column = 'table';
SELECT @Column, '%' + REPLACE(@Column, ' ', '%') + '%', CASE WHEN @Sentence LIKE '%' + REPLACE(@Column, ' ', '%') + '%' THEN 1 ELSE 0 END;
SELECT @Column = 'field of red apple';
SELECT @Column, '%' + REPLACE(@Column, ' ', '%') + '%', CASE WHEN @Sentence LIKE '%' + REPLACE(@Column, ' ', '%') + '%' THEN 1 ELSE 0 END;

That should also work with SQLite, e.g.:

SELECT [RowNumber] FROM [Table] WHERE 'the red apple on the table in the middle of the field' LIKE '%' + REPLACE([ColumnText], ' ', '%') + '%'

Upvotes: 1

Related Questions