mysql og REGEXP

[OyvindN]

Skal hente ut alle titler med liten bokstav og en stor bokstav rett ved siden av hverandre.

 

Har prøvd noe alá dette:

 

SELECT title FROM item WHERE title REGEXP '[a-z][A-Z]'

 

Funker dårlig, noen som har bedre forslag?

[Ekko]

Prøv

 

SELECT title FROM item WHERE title REGEXP '.*[a-z][A-Z].*'

 

 

EDIT:Forresten, så denne:

 

http://dev.mysql.com/doc/refman/5.1/en/regexp.html

 

REGEXP is not case sensitive, except when used with binary strings.

 

Edit2: Les også dette:

 

This sql statements:

 

SELECT 'WORD' REGEXP '[[:upper:]]{4}'; # => 1;

SELECT 'WORD' REGEXP '[[:lower:]]{4}'; # => 0

work right only when collate is _cs and NOT _ci (case insensitive)

 

created tables eg. the collate 'latin1_swedish_ci' have to be changed, if you want to use case sensitive REGEXPs like [[:upper:]] or [[:lower:]]!

ALTER TABLE <name> CONVERT TO CHARACTER SET latin1 COLLATE latin_general_cs

 

I set in my.cnf now:

[mysqld]

default-collation= latin1_general_cs

 

#default was latin1_swedish_ci

 

 

Posted by Koy Kragh on March 4 2006 7:33pm [Delete] [Edit]

 

The above post by Guido Dieterich (about collation and case sensitivity) is a good point. However, there is a way to match in a case-sensitive manner without having to change the collation of your existing tables: use the "BINARY" keyword.

 

Here's an extended example (based on the one previously posted):

 

SELECT

('WORD' REGEXP '[[:upper:]]{4}') AS `upper_match`, # this will be a 1

('WORD' REGEXP '[[:lower:]]{4}') AS `lower_match`, # this will be a 1 on an "*_ci" collation

# -BINARY- matches below

(BINARY 'WORD' REGEXP '[[:upper:]]{4}') AS `bin_upper_match`, # this will be a 1

(BINARY 'WORD' REGEXP '[[:lower:]]{4}') AS `bin_lower_match` # this will be a 0 even on an "*_ci" collation

 

 

Posted by Dennis K on November 8 2006 10:04am [Delete] [Edit]

 

The query "SELECT * FROM table WHERE text REGEXP 'UPPER'" on a *.ci (e.g. latin1_general_ci) table will find any case insensetive words, even words like "upper" or "uPpOr", or "UpPOr", etc...

 

To avoid this use one of the following Methods:

 

SELECT * FROM table WHERE text COLLATE latin1_general_cs REGEXP '...'

 

OR

 

SELECT * FROM table WHERE CAST(x AS BINARY) REGEXP '...'

Endret 8. januar 2009 av Ekko

[OyvindN]

REGEXP BINARY var løsningen gitt.