String search with accented characters.

[Paul Pichette]

I would like to search a table field which contains accented characters.
Is there a way to specify that the search be accent-insensitive?

Just curious,

Paul

 

[jdpjamesp]

So your table data says surité but you want surite to match for example?

 

[Paul Pichette]

Exactly.

all accents like àÀéÉçè will make for a search to make a difference in a MATCHES function.

Progress allows you to setup a field as 'Case-Sensitive', but warn you of the adverse effects on dataservers and such, but there's no option to control accents.

I looked into a function CODEPAGE-CONVERT, but I can't seem to make it work for me. I'm not too knowledgeable about code pages, so I'm not even sure if this function is what I need.

Of course, I could do my own function that would parse a string and substitute any accents to replace them with the accent-free equivalent à - a, é - e, etc... This will be very slow performance for sure.

I would think some people must have faced this kind of request before no?

 

[RealHeavyDude]

Disclaimer: I have not tested this so it might not work as I think - it is just a thought of mine!

I don't think it is a code page issue - IMHO it is a collation issue. Collation like code pages is a component relevant to internationalization. It gives each character in a code page a sort weight thus effectively defining the sort order. If the German Umlaut A has the same weight as A then sorting will treat them as if they were the same. Therefore you have different collations for different language regions which most obviously affects the sort order for special characters - like the German Umlaut A for example.

AFAIK the sort weight does not only affect sorting, it also affects whether characters are treated differently in string comparisons. Therefore I believe that picking the right collation could solve your problem. You need to be aware that the collation table is stored in the database and you can override it in data retrieval statements like FOR EACH.

Heavy Regards, RealHeavyDude.

 

[Paul Pichette]

Interesting stuff... So collation tables are used to sort & compare characters.

I found a function called COMPARE, which seems to handle comparison based on a collation table, and I noticed the last argument there collation can be specified. What values are available?

I think SQL server handles Accent-Insensitive data: http://msdn.microsoft.com/en-us/library/ms180175.aspx

 

[RealHeavyDude]

You might find the following knowledge base article interesting:

http://progress.atgnow.com/esprogre...ion-table+differences&resultType=5002#Goto237

This article also contains links to other articles of interest.

Heavy Regards, RealHeavyDude.