June 22, 2005 at 2:39 pm
I have an issue that's been bothering me for a little while now, so I'm finally turning to the gurus on this forum for help.
I have a stored proc that returns a dataset, but it's not quite sorted properly, and the reason is because of the content/datatype of the field I want to sort by. This field is a varchar that contains values in the format "x-x-x" or "x.x.x", where x can be either letters, numbers, or both. I would like to impose some custom sorting rules on that field, but haven't been able to figure out how yet.
There are several rules I'd like to apply, but we'll start with the most obvious. Let's just go with the case that I'm only dealing with numbers. It'll sort fine, as long as the number of digits between each delimiter is the same. However, since the field is a varchar, "1-1-10" will sort before "1-1-2". Does anybody know if it's possible to write a custom comparison function and use that as my ORDER BY case?
Matt
June 22, 2005 at 2:47 pm
of course it is:
ex:
select *
from table
order by '0'+replace(fld, '-', ' 0')
* Noel
June 22, 2005 at 2:57 pm
actually, if you know what is the maximum no of digits and that the numbers are always present then
select *
from table
order by
right('0000' + parsename(replace(fld,'-','.') ,3),4) +
right('0000' + parsename(replace(fld,'-','.') ,2),4) +
right('0000' + parsename(replace(fld,'-','.') ,1),4)
* Noel
June 22, 2005 at 3:34 pm
Unfortunately, there's no standard, so I can't assume anything about the data or how many digits it contains. Contextually, these data are somewhat hierarchical, so I might have "1-1-2" in one of them, and "1-1-2-1" in another.
Your first solution would work well, given that my data is all numerical. However, if you read my original post, it can also have letters in there. Let's use your first solution for an example of that. If I'm compring "A-1-2" and "A-1-10", then they would be compared at "A0102" and "A01010". The letter "A" being in there still forces it to be a string comparison, and "1" will always sort before "2" that way.
June 22, 2005 at 3:40 pm
Like I tried to said you can create you custom logic like:
select *
from table
order by fn(fld)
but the best approach is to materialize that function at insert time and use that field in the order by
* Noel
June 22, 2005 at 8:14 pm
If applicable you can add another column in the table sort order.
SELECT* from Table ORDER BY SortOrder
Regards,
gova
June 23, 2005 at 6:05 am
Adding a field to use as a sort key is not an option. This is an existing database with about 2 years' worth of data in it.
Noel, you say that I can use a function as my custom sort logic, but I'm not quite sure how it would work. I don't know how SQL Server handles a sort internally, so I don't know how I need to setup the function.
For instance, I'm assuming my function would have to contain the logic for comparing two values. In the application as it currently exists, I already have that in VBScript form, I'd just need to convert it to T-SQL. The problem is that, logically, this function needs two parameters - the two that are being compared. You suggested creating a function that takes the field as a parameter. So my question is, what would the function then have to look like? I guess I'm just confused as to how my function knows what data to compare and what SQL Server is expecting in return.
June 23, 2005 at 7:07 am
The function will not compare values, it will convert all values to a common format. For example:
FN('1-1-2') could return '001-001-002'
FN(1-1-2-1) could return '001-001-002-001'
FN('A-1-2') could return 'A-001-002'
FN('A-1-10') could return 'A-001-010'
Of course, since I'm not familiar with all possible forms of your data, so this is just an example. The point is, to use a user-define function, you must be able to convert any given value to a common format that will sort the way you want it to.
June 23, 2005 at 7:11 am
Ah, now I get it. Thanks a lot.
June 23, 2005 at 7:15 am
You will have to assume at least something, otherwise it is hard to implement sorting... What about 6 or 10 digits for each number between the delimiters, wouldn't that be enough in all cases? Then you could think along the above suggested lines, how to use a function to create values that can be compared. As mkeast wrote, unless you are able to define conversion to comparable values, there is no way to sort the data correctly.
June 23, 2005 at 7:21 am
This assumes a maximum of four values with either hyphen or full stop delimiter and a maximum of 4 chars between delimiters
ORDER BY
ISNULL(RIGHT('0000'+PARSENAME(REPLACE([column],'-','.'),4),4),'')+
ISNULL(RIGHT('0000'+PARSENAME(REPLACE([column],'-','.'),2),4),'')+
ISNULL(RIGHT('0000'+PARSENAME(REPLACE([column],'-','.'),3),4),'')+
ISNULL(RIGHT('0000'+PARSENAME(REPLACE([column],'-','.'),1),4),'')
Far away is close at hand in the images of elsewhere.
Anon.
June 23, 2005 at 7:31 am
The thing that might end up being my stopping block is the fact that I can't really assume anything. The way I have it currently written sorts an array on the client side. That series of functions sorts it by splitting it on the delimiter, then comparing corresponding items. Obviously, at that point, I can take everything that's just a number and compare it numerically. I can also compare letter to letter properly. Past that, I just programmed a rule to have letters sort before numbers, in the rare case that I would have 1-A-1 and 1-1-1. Not that I've seen this with the data yet, but I also programmed in the ability to sort 1-A2-1 and 1-A10-1 properly as well. All of it is accomplished with arrays and recursive function calls. It really is quite ugly, but may end up being my solution after all.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply