Any way to force AccentSensitivity?

[MCMartin]

In a database with AccentSensitive=off the Replace function treats 'a' and 'ä' the same. So does any way exists to temporarily force AccentSensitivity for the Replace function?

[Former Member]

If you are just trying to come up with a 'key' to do sorting on you might wnat to review the "sort_collation" option but it sounds like you are intent upon some sort of translation.

As far as I can tell, that would need a collation-tailoring to accomplish this and there are only the 2 [Strng] functions SORTKEY( ) and COMPARE( ) that have that support. Neither of those will accomplish this by themselves.

It seems to require custom logic. External function logic may be a better solution.

[MCMartin]

yes we now use a external .net function to do the trick

[VolkerBarth]

Treating the whole string as binary (and therefore case sensitivity and the like) would be too strong, I guess? Otherwise, replace() with binary datatypes might do the trick.

[MCMartin]

I think binary replace is available from SQLA 17 up

[VolkerBarth]

Yes, I should have known better, as I asked that a while ago:

How do builtin string functions work with binary data?

and Mark has explained that REPLACE() (pre-v17) does only work with character data and would convert binary input into character data...