[V4RB2] SQL REGEX Help

[Eric Ferrer]

Le 29 juin 2005 à 17:42, Ruslan Zasukhin a écrit :

> Ok, so AT LEAST now
>
>     indexes, searches, sorting, LIKE -- take care on collation
>     regex -- no

wow, I feel confused now...

What are the differences between a LIKE and a REGEX search?

I thought that the LIKE keyword was deprecated in v2...


Anyway, I've just made some tests and here are some simple results:
Note that the field 'Name' in the table 'Clients' has been set as  
"kPrimary" in the CollationAttribute "kStrength"

SELECT Name FROM Clients
WHERE Name REGEX 'eme'
--> returns clients such as "Diop Systeme", "France Espace  
Developpement" and "Maracadé Evénement"
--> but it does not return clients such as "7ème Apache Films"

SELECT Name FROM Clients
WHERE Name REGEX 'ème'
--> returns only clients with exactly 'ème' in their name, such as  
"Le Public Système", "7ème Apache Films"

SELECT Name FROM Clients
WHERE Name LIKE 'eme'
--> return absolutely no record at all

SELECT Name FROM Clients
WHERE Name LIKE 'ème'
--> return absolutely no record at all

SELECT Name FROM Clients
WHERE Name REGEX '(?i)[eè]me'
--> this one returns expected records, such as "16/9 EME", "Le Public  
Système", "Vivement Lundi"


Eric