Alphabetically rules for ordering columns

[mabho]

Hi. Recently I noticed that when ordering rows in a text column, SQL Server will put "A Place to go" before "Abort", for example. It considers the white space as a character that comes earlier than any other letter. Well, I don't know if that is a rule when ordering elements in english, but in portuguese that is pretty strange. Is there a workaround for that? The right thing to be done for ordewring elements in this case would be to IGNORE white space, so that order would be like "Abort", "Acme", "A crash in the street", "A fight in the club", "Ambar".

Thanks for any help.

 

[gmmastros]

Instead of

Order By [!]Field[/!]

You could:

Order By [!]Replace(Field, ' ', '')[/!]

Your performance will suffer. Try it and see if the performance is acceptable.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mrdenny]

What collation are you using for the column?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[mabho]

Hi. I have executed "EXEC sp_helpsort" query and the response was:

"Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data".

I have read an article (with no practical examples) where I understand I should order elements in dictionary order, not binary order. How can I reconfigure my table or even my whole database for Dictionary Order?

Thanks a lot for the answers.

 

[gmmastros]

You can get a list of collations that SQL Server supports, by running this...

SELECT *
FROM ::fn_helpcollations()

So, I tried different collations to see if any of them would affect the sort order. Maybe I did something wrong, but none of the collations that I tried (and I tried a lot of them) affect the sort order for your data. Here's what I did...

Declare @Temp Table(data VarCHar(100))

Insert Into @Temp Values('Abort')
Insert Into @Temp Values('Acme')
Insert Into @Temp Values('A crash in the street')
Insert Into @Temp Values('A fight in the club')
Insert Into @Temp Values('Ambar')

select * from @Temp order By data collate Latin1_General_BIN
select * from @Temp order By data collate Latin1_General_BIN2
select * from @Temp order By data collate Latin1_General_CI_AI
select * from @Temp order By data collate Latin1_General_CI_AI_WS
select * from @Temp order By data collate Latin1_General_CI_AI_KS
select * from @Temp order By data collate Latin1_General_CI_AI_KS_WS
select * from @Temp order By data collate Latin1_General_CI_AS
select * from @Temp order By data collate Latin1_General_CI_AS_WS
select * from @Temp order By data collate Latin1_General_CI_AS_KS
select * from @Temp order By data collate Latin1_General_CI_AS_KS_WS
select * from @Temp order By data collate Latin1_General_CS_AI
select * from @Temp order By data collate Latin1_General_CS_AI_WS
select * from @Temp order By data collate Latin1_General_CS_AI_KS
select * from @Temp order By data collate Latin1_General_CS_AI_KS_WS
select * from @Temp order By data collate Latin1_General_CS_AS
select * from @Temp order By data collate Latin1_General_CS_AS_WS
select * from @Temp order By data collate Latin1_General_CS_AS_KS
select * from @Temp order By data collate Latin1_General_CS_AS_KS_WS

In EVERY case, "A crash in the street" is the first record returned.

In this example, you can sort the data the way you want with this...

Select * 
From   @Temp 
Order BY Replace(data, ' ', '')

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mrdenny]

gmmastros, all the collations that you used are Latin1_General. mabho has used the default Collation which is based on the English language.

Mabho, you'll want to do the:

SELECT *
FROM ::fn_helpcollations()

This will give you a list of the available collations. Look to see if there are any installed on your machine that reference "Portuguese". If not see if you can find any other languages that have the same sorting rules as Portuguese. The collations that are available will depend on the language packs that are installed on the Windows OS. (As I'm based in the US I just have the default US Languages installed, and aparently Portuguese isn't installed).

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[harebrain]

To add to gmmastros' replies, you could circumvent performance degradation by adding a column that already has the spaces squeezed out and sort on that. It follows that in the example he gives, you'd do the replace on insertion into the temp table; then you'd ORDER BY column_name without a function. Which also would make an index useful, as it wouldn't be when you ORDER BY function(column_name).

 

[gmmastros]

Denny,

I tried *almost* every collation and couldn't find one that sorts the way Mabho wants (with a space sorted after the letters in the alphabet). The list I showed was an abbreviated list of all the collations available on my system.

I'm interested to see how this turns out.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mabho]

Well, thanks for all your replies and help. It is so weird to understand there is no system support in SQL Server for a rule I learned when I was 10 years old.

Well, I still tend to believe the system must support that in some sort of collation.

George, I applied the workaround you suggested and it worked properly. Thank you. I have a database with sort of 1,200 records and it is unlike that it will grow more than that. So maybe it is not a huge problem to apply that rule.

Harebrain, your suggestion is nice also. What SQL Query could duplicate the column killing all white spaces? I would have to create that column in my database from the data already in there. I can change my application to store titles with no white spaces in that column.

Again, thanks for all of you.

 

[mrdenny]

Following allong Harebrain's idea...
You could setup a computed column on the table that stripts out the spaces. This way you would only need to change the order by column in your code.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[fredericofonseca]

What programs are you using to access the tables? web based or VB/C based?

Latin1_General_CI_AI is the correct collation order to use with Portuguese data, but in order to get a "correct" order one of two things must be done.

1- Windows client must be using the Portuguese locale

or

2- Client application must set LCID to 0x816 (or 1046)

for example using a application connecting using ADO the following should be done.
Connection_object.Properties("Locale Identifier") = 1046


For all others non Portuguese people here.
Portuguse collation order should be (doesnt mean windows does it)
all letters (regardless of accents or case) from a-z, numbers, other special characters.
Strings with "'" as "o'Conner" should be treated as "odonnel". Other rules like this apply. Dont have them at hand unfortunatelly.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[mrdenny]

I forgot all about the Language option defined on the login. You can probably also set this to Portuguese (which is on the list) which "should" give you the same as setting the connection object Locale Identifier.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[mabho]

Hi. The program accessing the DB is DotNetNuke web based CMS.

 

[mrdenny]

That should be fine. You just want to change the language for what ever login the CMS software is using to log into the database.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com