CODEWITHSUNDEEP
sqlitefull-text-search
Nhu Nguyen
Nhu Nguyen

Reputation: 874

MATCH in SQLite FTS

I use FTS in SQLite, I have data:

abc
def
go to
to go
jdk
sqlite
let go
go on
going to
not going

I use query: select * from table where columns MATCH 'go*'. RESULT:

go to
to go
let go
go on
going to
not going

But I want result go to, go on, how to query?

Upvotes: 2

Views: 8601

Answers (4)

Naresh Reddy
Naresh Reddy

Reputation: 1

select * from table where columns MATCH '^go *'

This will give the result: go to, go on

select * from table where columns MATCH '^go*'

This will give the result: go to, go on, going to

Upvotes: 0

yelliver
yelliver

Reputation: 5926

select * from table where columns MATCH 'go*'

Match all phrases that contain any single word which start with go

go to

going to

here we go

...

select * from table where columns MATCH 'go'

Match all phrases that contain any single word which equal go

go to

here we go

...

select * from table where columns MATCH '"^go"'

Match all phrases that start with a single word which equal go

go to

go in

...

Someone ansewers '^go' but it does not work, the magic is ' "^go" '

Upvotes: 2

boscowitch
boscowitch

Reputation: 428

What also might help a bit is using '^go' which will only give you the entrys which beginn with go (supported from sqlite version 3.7.9)

Upvotes: 1

CL.
CL.

Reputation: 180010

As the documentation explains, go* is a prefix search and finds any words that begin with go.

You want to search for two words, so in theory, you have to do a phrase search. However, "go *" does not work because a prefix search needs an actual prefix.

Your best bet would be to search for the word go and, in your code, filter out any returned records where go occurs only at the end.

Upvotes: 3

Related Questions