sql query relative searching to previous searched words

I have list of word in table. I want to 开发者_Python百科search for all records contain e.g. book and books, pen and pens, that means, for all the word which ends with 's'. The query should show the word without 's' and the word with 's' too. not a query "SELECT * FROM words WHERE word LIKE '%s'" schema definition is,

words = <word, part_of_speech>

I have to search on 'word' How can I do this? The result could be,

book
books
pen
pens

Its something like, if there is a value in the colum as 'word' and there is another value as 'word'+'s' then show the rows of both 'word' and 'word'+'s'. I'm using sqlite.

SELECT word FROM words WHERE word LIKE 'book%'

will match 'book', 'books', 'bookmark', etc

if you want to search for only a specific sufix then try

SELECT
    *
FROM
    words
WHERE
    word = '%s'
    or word = '%s' || 's' #change 's' to any addition you want to try

Google the "Porter Stemming Algorithm" and apply it to your data before you load it. This algorithm is as close as you can get to converting not just plurals but many other forms of word to a single word. e.g., "scholarly" becomes "scholar" and stuff like that.

If that does not meet your quality standards, because it will not trap for "mice" and other examples given in other answers, you will have to find a "stemming file". I know of no free ones (which does not mean there are none), but the one we use at my shop is part of a commercial package, so I've never had to find a free one.

At any rate, once you have applied the stemming to the words on the way in, you no longer have to search for multiple versions of a word, you just search for the stem.