[thelist] sorting question
rudy
r937 at interlog.com
Tue Feb 19 23:22:01 CST 2002
>... a similar method could certainly be used for MS SQL Server.
> Just look for a function which will truncate the decimal place off
> of a number.
that'd be the floor() mathematical function
sadly, it is a poor choice in this instance, as them aren't numbers to
start with, joel said them are "accounting thingies" and therefore probably
strings
paul's suggestion hinges on the convert() function turning strings into
numbers, which is awkward and might perform poorly, but i have no reason to
think it won't actually work
another method works only with strings --
split the thingie string using charindex to find the decimal point
left side is
substring( accno, 1, charindex('.',accno)-1 )
and that's your major sort field
right side is
substring( accno, charindex('.',accno)+1
, len(accno)-charindex('.',accno) )
pad the right side with, oh, let's say five zeroes to the left, then take
the rightmost five-char substring
right( '00000' + substring(accno,charindex('.',accno)+1
, len(accno)-charindex('.',accno))
, 5)
and that's your minor sort field
caution: untested -- you might have to adjust +1 or -1 ;o)
> sorry i can't be of more help with MS SQL Server. Not my strongpoint.
me neither, but i have a release 6 textbook that stands me in good stead
also, there's BOL (books online), available as a free (~38meg) download,
but it's upstairs on the kids' computer and they're asleep...
rudy
More information about the thelist
mailing list