1

i have table with only one field ex: title (varchar:255) and set as FULLTEXT index

I want to delete record that contain Only One word

Record

  • Oneword
  • two word
  • Three word count
  • this is contain four word
  • removethis

Desired Result after remove

  • two word
  • Three word count
  • this is contain four word

Thank you

Andy
  • 393
  • 1
  • 5
  • 17
  • Duplicate. Please see http://stackoverflow.com/questions/748276/using-sql-to-determine-word-count-stats-of-a-text-field – BahmanM Oct 08 '13 at 01:57
  • How about if the column contains only space(s)? Should it be deleted? – JScoobyCed Oct 08 '13 at 02:00
  • @BahmanM: it is not really complex as that solution, i just want to delete One word only, not store/need word count. The RealityGone samrt solution is work for me. Thank you – Andy Oct 08 '13 at 03:01

1 Answers1

1

This uses a wildcard match and will delete any rows where there is not a space in the text:

DELETE FROM tbl
WHERE col NOT LIKE '% %'

MySQL Manual - LIKE Operator

Mike D.
  • 4,034
  • 2
  • 26
  • 41
  • I'd use in the clause a `TRIM(col) NOT LIKE '% %'` to remove column containing only space (not in the OP specifications but it seems he tries to do data cleaning) – JScoobyCed Oct 08 '13 at 02:01
  • Not all languages use SPACE as the word delimiter. – BahmanM Oct 08 '13 at 02:03
  • @BahmanM. The example data used a space as the word delimiter. – Mike D. Oct 08 '13 at 02:22
  • Yes, it just simple text, asume as clean title only a-z, number – Andy Oct 08 '13 at 02:55
  • @RealityGone, sure. What I mean is that the solution is correct but not complete. – BahmanM Oct 08 '13 at 03:04
  • Yes it is. The solution doesn't have to work for "every language that ever existed or could exist". That would be called premature optimisation. Also, a quick google shows that all modern languages use a space to separate words except for symbol based languages like Kanjii. Regardless of if there are this would still be premature optimisation since it is unnecessary for the data being used. – Mike D. Oct 08 '13 at 03:09
  • I'm pretty sure that "premature optimisation" is the wrong term here. Without further knowledge of the original poster's actual use case, blithely ignoring the simple fact that not all writing systems separate with spaces is a recipe for difficult updates later on. Examples of these include 汉字-based systems (which have users numbering well over a billion) and scripts like Thai, among others. – JUST MY correct OPINION Oct 08 '13 at 06:28
  • "However, PrematureOptimization can be defined (in less loaded terms) as optimizing before we know that we need to." http://c2.com/cgi/wiki?PrematureOptimization – Mike D. Oct 08 '13 at 06:42