CODEWITHSUNDEEP
mysqlregex
Michael
Michael

Reputation: 4390

Regular Expression to match single, whole words from MySQL Database

I require a Regular Expression to match single words of [a-zA-Z] only with 6 or more characters.

The list of words:

a
27
it was good because
it
cat went to
four
eight is bigger
peter
pepper
objects
83
harrison99
adjetives
atom-bomb

The following would be matched

pepper

objects

adjectives

It isn't too bad if the following are matched too as I can strip out the duplicates after

it was good because

eight is bigger

atom-bomb

So far I have this

/[a-zA-z]{6,}/g

Which matches 6 character or more words according to RegEx 101 but when I use it in my MySQL database to filter a table on matches RegExp.

  1. Currently it includes words containing punctuation and other non a-zA-Z characters. I don't want it to include results with numbers or punctuation.
  2. In an ideal world I don't want it to include a row that contains more than one word as usually the other words are already duplicates.

Actual MySQL results

If a row contains a number or punctuation and/or contain one or more words I don't want it included.

I want only results that are single, whole words of 6+ characters

Results

Upvotes: 1

Views: 725

Answers (1)

Jonny 5
Jonny 5

Reputation: 12389

Drop the delimiters and add anchors for start/end:

SELECT 'abcdef' REGEXP '^[a-zA-Z]{6,}$'

-> 1

SELECT 'a abcdef' REGEXP '^[a-zA-Z]{6,}$'

-> 0

Also I changed [a-zA-z] in the character class to [a-zA-Z].

Instead of [a-zA-Z] you can also use [[:alpha:]] POSIX style bracket extension. So it becomes:

^[[:alpha:]]{6,}$

(As a side note: MySql uses Henry Spencer's implementation of regular expressions. if word boundaries needed, use [[:<:]]word[[:>:]] See manual for further syntax differences)

Upvotes: 3

Related Questions