I've searched and have not found any good answers to this. Maybe there
isn't one...
I need to have a string that when sorted with an ORDER BY, it comes after
the letter 'z'.
The database is created with the collation SQL_Latin1_General_CP1_CI_AS.
I know it is best to not rely on this and use some sort of surrogate field
to order the rows, but I cannot do that in this case - the data is being
used by SharePoint (MOSS) in a Business Data Catalog (BDC) column.
SharePoint will sort one of it's lists by the BDC column value, but it sorts
only by the values in that column.
In ASCII, I could use the tilde (~), but SQL is putting the tilde first in
the results.
Is there any character I could put at the beginning of the string so it
would fall out last in the query results?
(Remember, it is SharePoint generating the query, so I cannot change the SQL
it uses. I only control the values in the column.)I assume the column is char or varchar? If so, you can use the value
CHAR(208), for example,
Set NoCount ON
Create Table #FooBar(Foo varchar(10))
Insert #FooBar (Foo) Values ('A')
Insert #FooBar (Foo) Values (Char(208))
Insert #FooBar (Foo) Values ('z')
Select Foo, Ascii(Foo)
From #FooBar
Order By Foo
go
Drop Table #FooBar
You can find all the characters which will sort after 'z' with the following
code
Set NoCount ON
Create Table #FooBar(Foo varchar(10))
Insert #FooBar (Foo) Values ('A')
Insert #FooBar (Foo) Values ('z')
Declare @.i int
Set @.i = 1
While @.i < 256
Begin
Insert #FooBar (Foo) Values (Char(@.i))
Set @.i = @.i + 1
End
Select Foo, Ascii(Foo)
From #FooBar
Where Foo > 'z'
Order By Foo
go
Drop Table #FooBar
Tom
"JD" <yazoo@.newsgroup.nospam> wrote in message
news:%23uiqZB3nIHA.3556@.TK2MSFTNGP04.phx.gbl...
> I've searched and have not found any good answers to this. Maybe there
> isn't one...
> I need to have a string that when sorted with an ORDER BY, it comes after
> the letter 'z'.
> The database is created with the collation SQL_Latin1_General_CP1_CI_AS.
> I know it is best to not rely on this and use some sort of surrogate field
> to order the rows, but I cannot do that in this case - the data is being
> used by SharePoint (MOSS) in a Business Data Catalog (BDC) column.
> SharePoint will sort one of it's lists by the BDC column value, but it
> sorts only by the values in that column.
> In ASCII, I could use the tilde (~), but SQL is putting the tilde first in
> the results.
> Is there any character I could put at the beginning of the string so it
> would fall out last in the query results?
> (Remember, it is SharePoint generating the query, so I cannot change the
> SQL it uses. I only control the values in the column.)
>|||JD
select * from #FooBar order by case when foo ='z' then 1 else 2 end
"JD" <yazoo@.newsgroup.nospam> wrote in message
news:%23uiqZB3nIHA.3556@.TK2MSFTNGP04.phx.gbl...
> I've searched and have not found any good answers to this. Maybe there
> isn't one...
> I need to have a string that when sorted with an ORDER BY, it comes after
> the letter 'z'.
> The database is created with the collation SQL_Latin1_General_CP1_CI_AS.
> I know it is best to not rely on this and use some sort of surrogate field
> to order the rows, but I cannot do that in this case - the data is being
> used by SharePoint (MOSS) in a Business Data Catalog (BDC) column.
> SharePoint will sort one of it's lists by the BDC column value, but it
> sorts only by the values in that column.
> In ASCII, I could use the tilde (~), but SQL is putting the tilde first in
> the results.
> Is there any character I could put at the beginning of the string so it
> would fall out last in the query results?
> (Remember, it is SharePoint generating the query, so I cannot change the
> SQL it uses. I only control the values in the column.)
>|||As I said, I cannot change the query, since Sharepoint is doing the query.
I can only affect the data in the column.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uVGG%2305nIHA.4328@.TK2MSFTNGP03.phx.gbl...
> JD
> select * from #FooBar order by case when foo ='z' then 1 else 2 end
>
> "JD" <yazoo@.newsgroup.nospam> wrote in message
> news:%23uiqZB3nIHA.3556@.TK2MSFTNGP04.phx.gbl...
>> I've searched and have not found any good answers to this. Maybe there
>> isn't one...
>> I need to have a string that when sorted with an ORDER BY, it comes after
>> the letter 'z'.
>> The database is created with the collation SQL_Latin1_General_CP1_CI_AS.
>> I know it is best to not rely on this and use some sort of surrogate
>> field to order the rows, but I cannot do that in this case - the data is
>> being used by SharePoint (MOSS) in a Business Data Catalog (BDC) column.
>> SharePoint will sort one of it's lists by the BDC column value, but it
>> sorts only by the values in that column.
>> In ASCII, I could use the tilde (~), but SQL is putting the tilde first
>> in the results.
>> Is there any character I could put at the beginning of the string so it
>> would fall out last in the query results?
>> (Remember, it is SharePoint generating the query, so I cannot change the
>> SQL it uses. I only control the values in the column.)
>>
>|||Thanks - it is nvarchar, so I made a few changes to your example and found a
series of Unicode characters that sort after 'z':
set nocount on
create table #FooBar(Foo nvarchar(10))
insert #FooBar (Foo) values ('A')
declare @.i int
set @.i = 900
while (@.i < 1200)
begin
insert #FooBar (Foo) values (nchar(@.i))
set @.i = @.i + 1
end
insert #FooBar (Foo) values ('z')
select Foo, unicode(Foo)
from #FooBar
where Foo > 'z'
order by Foo
drop table #FooBar
"Tom Cooper" <tomcooper@.comcast.no.spam.please.net> wrote in message
news:O6sC0c3nIHA.4832@.TK2MSFTNGP06.phx.gbl...
>I assume the column is char or varchar? If so, you can use the value
>CHAR(208), for example,
> Set NoCount ON
> Create Table #FooBar(Foo varchar(10))
> Insert #FooBar (Foo) Values ('A')
> Insert #FooBar (Foo) Values (Char(208))
> Insert #FooBar (Foo) Values ('z')
> Select Foo, Ascii(Foo)
> From #FooBar
> Order By Foo
> go
> Drop Table #FooBar
> You can find all the characters which will sort after 'z' with the
> following code
> Set NoCount ON
> Create Table #FooBar(Foo varchar(10))
> Insert #FooBar (Foo) Values ('A')
> Insert #FooBar (Foo) Values ('z')
> Declare @.i int
> Set @.i = 1
> While @.i < 256
> Begin
> Insert #FooBar (Foo) Values (Char(@.i))
> Set @.i = @.i + 1
> End
> Select Foo, Ascii(Foo)
> From #FooBar
> Where Foo > 'z'
> Order By Foo
> go
> Drop Table #FooBar
> Tom
> "JD" <yazoo@.newsgroup.nospam> wrote in message
> news:%23uiqZB3nIHA.3556@.TK2MSFTNGP04.phx.gbl...
>> I've searched and have not found any good answers to this. Maybe there
>> isn't one...
>> I need to have a string that when sorted with an ORDER BY, it comes after
>> the letter 'z'.
>> The database is created with the collation SQL_Latin1_General_CP1_CI_AS.
>> I know it is best to not rely on this and use some sort of surrogate
>> field to order the rows, but I cannot do that in this case - the data is
>> being used by SharePoint (MOSS) in a Business Data Catalog (BDC) column.
>> SharePoint will sort one of it's lists by the BDC column value, but it
>> sorts only by the values in that column.
>> In ASCII, I could use the tilde (~), but SQL is putting the tilde first
>> in the results.
>> Is there any character I could put at the beginning of the string so it
>> would fall out last in the query results?
>> (Remember, it is SharePoint generating the query, so I cannot change the
>> SQL it uses. I only control the values in the column.)
>>
>
