Collation - Special Character - Sybase

[Former Member]

Hi everyone,

I've migrated recently from MSSQL to Sybase.

Before I could do something like:

SELECT TOP 1 * FROM Customer WHERE (Customer.FirstName)='stephane' COLLATE SQL_Latin1_General_CP1_CI_AI

I used COLLATE because the Customer name can be Stephane but also Stéphane. In this case if somenone searched using "é" or "e" or "è", the queary result should be the same.

But I don't know if I can do the same in Sybase. I've tryed without sucess. I only want to ignore the accents.

Can anyone help me with this issue?

Cheers,

Frank

[VolkerBarth]

The COLLATE clause is not currently supported in SQL Anywhere (BTW: Which version do you use?).

But you can do something similar with the help of the COMPARE() function, cf. this FAQ:

• Can I use collation-tailoring without the COMPARE (or SORTKEY) function?

---

Note: In case you have a default collation that already ignores accents, then apparently you won't have to specify a different collation - then the default comparison would be enough. So what default collations does your database use?

That can be displayed via the DBINFO tool or with the according queries on database properties:

For the CHAR collation:

select db_property('Collation'),
  db_extended_property('Collation', 'AccentSensitive'),
  db_extended_property('Collation', 'CaseSensitivity'),
  db_extended_property('Collation', 'PunctuationSensitivity'),
  db_extended_property('Collation', 'Properties');

For the NCHAR collation:

select db_property('NCharCollation'),
  db_extended_property('NCharCollation', 'AccentSensitive'),
  db_extended_property('NCharCollation', 'CaseSensitivity'),
  db_extended_property('NCharCollation', 'PunctuationSensitivity'),
  db_extended_property('NCharCollation', 'Properties');

For example, in my case, accents are ignored by default, so the following sample query returns "equal names":

select 'equal names' from dummy where 'Stephane' = 'Stéphane'

[Former Member]

Hi,

Thank you very much for your ansewer.

My version is: Adaptive Server Enterprise/15.0.3/EBF 16745 ESD#2/P/X64/Windows Server/ase1503/2707/64-bit/OPT/Sun

Can I change the default database properties to ignore the accents? Or to do this I should rebuild the database?

I don't think COMPARE() function would help me in this case... I was looking for a more generic solution, so I could use it in other situations.

[former_SQLA_member1694880]

This forum is for ASA, not ASE.

[Former Member]

Hi,

This means I cannot post?

[VolkerBarth]

Please have a look at this forum's FAQ - there's an entry for ASE users.

Note: My answer does apply to SQL Anywhere, not to ASE.

[reimer_pods]

You might want to ask here: http://scn.sap.com/community/developer-center/oltp-db/content

[Former Member]

Thanks guys. And sorry for the misunderstanding.

[VolkerBarth]

No need to worry - it's rather common to speak of a "Sybase database" which might mean ASE, IQ, SQL Anywhere (formerly ASA), Advantage Server - and possibly other different DBMSes...