CODEWITHSUNDEEP
mysql
user1130861
user1130861

Reputation: 45

mysql statement with using the [charlist] Wildcard

Trying to use this sql statement. The first 2 parts work fine, I am trying in corporate charlist so that the string its pulling cannot contain any of the letters in it

SELECT * FROM table WHERE LENGTH(RTRIM(word)) = 8 AND word LIKE 'a__c____' AND word LIKE '%[!tesp]%' GROUP BY word

Basically I want this statement to pull up a word that is:

Upvotes: 0

Views: 2822

Answers (2)

tigretigre
tigretigre

Reputation: 46

This should be sufficient for the where clause to answer the problem as described:

word rlike '^a[a-z]{2}c[a-z]{4}$' and work not rlike '[tesp]'

although it could be collapsed into one regexp:

word rlike '^a[a-df-oqru-z]{2}c[a-df-oqru-z]{4}$'

I'd go with the two rlike form if I were you, for maintainibility for certain, and most likely for performance as well.

Upvotes: 0

mathematical.coffee
mathematical.coffee

Reputation: 56935

Use the REGEXP (or its synonym RLIKE) operator for this.

SELECT *
FROM table
WHERE word RLIKE '^a[^tesp]{2}c[^tesp]{4} *$'
GROUP BY word;

The [^tesp]{2} means "match 2 characters that are not t, e, s or p".

Note that the 8-letters is enforced by the regex. Also, it handles the RTRIM by the *$ at the end of the regex (allowing any number of spaces at the end).

This lets you just do one pass over each word instead of three (once for LENGTH, once for LIKE 'a__c____' and once for the "t","e","s","p").

Upvotes: 1

Related Questions