Differentiate 'ae' and 'æ' when query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Differentiate 'ae' and 'æ' when query

Author

Topic

Hotice
Starting Member

9 Posts

Posted - 2006-04-10 : 16:05:25

I have a field in one table, which contains characters like '%ae%', and '%æ%', I want to select from this table where this field like '%ae%' , but SQL Server will reture records that like '%ae%' and '%æ%'. SQL server treat 'ae' and 'æ'as the same. Is there a way to let SQL Server to treat 'ae' as not equal to 'æ'? I know that I need to change the collation. but I don't know how. I googled, but didn't find the way to solved the problem. Thank you for the help.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-04-11 : 05:48:59

Look up COLLATE..

Either that or use CHAR()..

There are examples in Books Online..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-11 : 06:53:21

Run this and see how many rows you get

Select * from
(
	select 'ae' as data union all
	select 'æ' 
) T
where data like '%ae%'

Madhivanan

Failing to plan is Planning to fail

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-04-11 : 09:06:51

quote:
Originally posted by madhivanan

Run this and see how many rows you get
Select * from
(
select 'ae' as data union all
select 'æ'
) T
where data like '%ae%'

I think that's rather the point: the result will depend on the database collation.

For example, I have two databases here, one with a database collation of SQL_Latin1_General_CP1_CI_AS (it used to be a SQL Server 7 database) and that returns one row.
Using the other, with a database collation on Latin1_General_CS_AS returns both rows.
The server collation is Latin1_General_CI_AS.

If you want to treat ae distinct from æ for linguistic reasons, you probably want to use something like Danish_Norwegian_CI_AI. All the _bin collations will treat them as distinct, of course, but they're not usually suitable for meaningful text.

In terms of changing collations, changing the server or database collation won't change the interpretation of anything in existing tables, since the collation is part of the specification of all character columns: if you didn't specify it when you created the column it defaults to the database collation. Changing the collation of a column is just a question of doing something like:

ALTER TABLE Customer ALTER COLUMN full_name varchar(100) COLLATE Danish_Norwegian_CI_AI

I believe you have to specify the column datatype again when you change the column collation.

Hotice
Starting Member

9 Posts

Posted - 2006-04-11 : 15:43:08

Thanks for all the replay. Collation Danish_Norwegian_CI_AS works.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-12 : 04:34:21

Thanks Arnold for your useful informations. You are Collation specialist

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources