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

asked 20 Dec '13, 05:02

frankmookie's gravatar image

frankmookie
13113
accept rate: 0%

edited 20 Dec '13, 07:37

Justin%20Willey's gravatar image

Justin Willey
7.6k137179249


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'
permanent link

answered 20 Dec '13, 05:08

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 20 Dec '13, 05:24

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.

(20 Dec '13, 05:54) frankmookie
Replies hidden

This forum is for ASA, not ASE.

(20 Dec '13, 06:09) Dmitri

Hi,

This means I cannot post?

(20 Dec '13, 06:13) frankmookie
Replies hidden
1

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.

(20 Dec '13, 06:41) Volker Barth
1
(20 Dec '13, 07:32) Reimer Pods

Thanks guys. And sorry for the misunderstanding.

(20 Dec '13, 08:13) frankmookie
Replies hidden

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...

(20 Dec '13, 08:35) Volker Barth
showing 4 of 7 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×53
×17

question asked: 20 Dec '13, 05:02

question was seen: 9,172 times

last updated: 20 Dec '13, 08:35