August 3, 2006 at 3:45 am
Hi,
I found that SQL 2000 ignores apostrophes during ordering.
Example:
create table test ([name] varchar(100) not null)
insert into test values ('first')
insert into test values ('second')
insert into test values ('''third')
insert into test values ('''another')
select * from test order by [name]
And the result is:
'another
first
second
'third
Any suggestions how to order with apostrophe? I mean I'd like result like this:
'another
'third
first
second
Many thanks
August 3, 2006 at 4:38 am
indeed odd.
Found it works with binary collation for this selection.
select * from test order by [name]
COLLATE Latin1_General_BIN
August 3, 2006 at 5:08 am
yes, that works. Annoying for me is fact that binary collation also distinguish small letters and capitals 🙁
August 3, 2006 at 6:13 am
That is because the binary collation is case sensitive by default. Hope this helps.
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
August 3, 2006 at 6:18 am
yes, you are right. But I need case insensitive collation and at the same time collation which uses apostrophes.
Thanks
Peter
August 3, 2006 at 7:01 am
No it doesn't but certain collations may make it look like it does.
What is the default collation you using?
Far away is close at hand in the images of elsewhere.
Anon.
August 3, 2006 at 7:16 am
Currently I have Latin1_General_CI_AI.
Peter
August 3, 2006 at 7:34 am
See
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B305704
Alternate to what is suggested you could try
ORDER BY ASCII(LOWER([name])), [name]
Far away is close at hand in the images of elsewhere.
Anon.
August 3, 2006 at 7:42 am
Thanks. And what do you think about performance of this "ASCII(LOWER[name], [name])" ? I plan to use this query on huge amount of text so maybe it'll be very slow.
Thanks Peter
August 3, 2006 at 7:54 am
Apostrophes are covered under ANSI SQL 92 delimited identifiers so I think you shoud use that if you data does not include ascent sensitive language because all the ascent sensitive collations are case sensitive. The links below covers how to use identifiers. Hope this helps
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_89rn.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_9jxu.asp
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply