3

I would like to perform a MySQL regular expression which will check a string for both singular and plural version of the string.

I am ignoring special characters and complex grammar rules and simply want to add or remove an 's'.

For example if the user enters 'shoes' it should return matches for both 'shoes' and 'shoe'. Conversely, if the user enters 'shoe' it should return matches for both 'shoe' and 'shoes.

I am able to optionally check against the plural version of the match as follows:

WHERE Store.tags RLIKE ('(^|,)+[[:space:]]*shoe(s)*[[:space:]]*(,|$)+')

I have been reading up about positive/negative look-ahead or look-behind and I seem to be constantly chasing my tail.

Lightness Races in Orbit
  • 378,754
  • 76
  • 643
  • 1,055
Richard
  • 422
  • 7
  • 17
  • See http://stackoverflow.com/questions/3088433/sql-plural-singular-searches – Álvaro González Sep 09 '11 at 11:42
  • Unfortunately I cannot use FULLTEXT matching due to the issue of having to match the string within a comma separated list of tags. Also, I will not be using normalisation either. – Richard Sep 09 '11 at 12:33
  • 4
    Oh well... That's why it's such a bad idea to store comma-separated lists of anything in a relational database :) Have you considered using your host language (Java, PHP or whatever) to pre-calculate the singular/plural forms and then use a simpler search expression? You could even use a simple `LIKE` clause! – Álvaro González Sep 09 '11 at 12:47

1 Answers1

0

Well if you're only asking about removing or adding an s ... make it simple as your requirements ...

Just check if last letter is an s.. if yes -> remove it for singular, if no, add one for plural

WHERE (a LIKE '%shoes%' OR  a LIKE '%shoe%')

It's as dumb as it gets but seeing you only need to check for s .. it's good enough.

Morg.
  • 697
  • 5
  • 7