Is there any way to put an invisible character at beginning of a string to change its sort order?

Is there any way to put a non printing or non obtrusive character at the beginning of a string of data in sqlserver. so that when an order by is performed, the string is sorted after the letter z alphabetically?

I have used a space at the beginning of the string to get the string at the top of the sorted list, but I am looking to do something similar to put a string at the end of the list.

I would rather not put another field such as "SortOrder" in the table to use to order the sort, and I would rathe开发者_如何学Pythonr not have to sort the list in my code.

Added: Yes I know this is a bad idea, thanks to all for mentioning it, but still, I am curious if what I am asking can be done

Since no one is venturing to answer your question properly, here's my answer

Given: You are already adding <space> to some other data to make them appear top

Solution: Add CHAR(160) to make it appear at the bottom. This is in reality also a space, but is designed for computer systems to not treat it as a word break (hence the name).

http://en.wikipedia.org/wiki/Non-breaking_space

Your requirements:

1. Without adding another field such as "SortOrder" to the table
2. Without sorting the list in your code

I think this fits!

create table my(id int,data varchar(100))
insert my
select 1,'Banana' union all
select 2,Char(160) + 'mustappearlast' union all
select 3,' ' +N'mustappearfirst' union all
select 4,'apple' union all
select 5,'pear'

select *
from my
order by ASCII(lower(data)), data

_{(ok I cheated, I had to add ASCII(lower( but this is closest to your requirements than all the other answers so far)}

You should use another column in the database to help specify the ordering rather than modifying the string:

SELECT *
FROM yourtable 
ORDER BY sortorder, yourstring

Where you data might look like this:

yourstring               sortorder
foo                      0
bar                      0
baz                      1
qux                      1
quux                     2

If you can't modify the table you might be able to put the sortorder column into a different table and join to get it:

SELECT *
FROM yourtable AS T1
JOIN yourtablesorting AS T2
ON T1.id = T2.T1_id
ORDER BY T2.sortorder, T1.yourstring

Alternative solution:

If you really can't modify the database at all, not even adding a new table then you could add any character you like at the start of the string and remove it during the select:

SELECT RIGHT(yourstring, LEN(yourstring) - 1)
FROM yourtable
ORDER BY yourstring

Could you you include something like:

"<SORT1>This is my string"
"<SORT2>I'd like this to go second"

And remove them later? I think using invisible characters is fragile and hacky.

You could put a sort order in the query and use unions (no guarantees on performance).

select 1 as SortOrder, *
from table
where ... --first tier
union
select 2, *
from table
where ... --second tier
order by SortOrder

In my opinion, an invisible character for this purpose is a bad idea because it pollutes the data. I would do exactly what you would rather not do and add a new column.

To modify the idea slightly, you could implement it not as a sort order, but a grouping order, defaults to 0, where a negative integer puts the group at top of the list and a positve integer at the bottom, and then "order by sort_priority, foo"

I'm with everyone else that the ideal way to do this is by adding an additional column for sort order.

But if you don't want to add another column, and you already use a space for those items you want to appear at the top of the list, how do you feel about using a pipe (|) for items at the bottom of the list?

By default, SQL Server uses a Unicode character set for its sorting. In Unicode, the pipe and both curly brackets ({, }) come after z, so any of those three characters should work for you.