on 2013 Dec 20 5:02 AM
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
Request clarification before answering.
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:
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'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You might want to ask here: http://scn.sap.com/community/developer-center/oltp-db/content
User | Count |
---|---|
75 | |
30 | |
9 | |
8 | |
7 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.