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

Question

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 rather 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

Answer 1

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)}

Answer 2

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