doesn't sort your roman numerals though........
Scott Thornton, Programmer
Application Development
Information Services and Telecommunications
Hunter-New England Area Health Service
Phone RNH +61 2 49236078 JHH +61 2 49214193
Fax +61 2 49214191
[EMAIL PROTECTED]
>>> [EMAIL PROTECTED] 02/16/05 11:27 AM >>>
the solution is this:
part 1:
CREATE FUNCTION dbo.SortIt (@OriginalDocNo as varchar(500), @Part as
varchar(1))
RETURNS integer AS
BEGIN
Declare @DotPos int
Declare @RealNo int
Declare @test varchar(500)
set @DotPos = PATINDEX('%.%', @OriginalDocNo)
set @RealNo = 0
if @DotPos=0
return @DotPos
if @Part='L'
begin
Set @test = left(@OriginalDocNo, @DotPos-1)
if isnumeric(@test)=1
begin
set @RealNo = @test
return @RealNo
end
end
if @Part='R'
begin
Set @test = right(@OriginalDocNo, Len(@OriginalDocNo) -
@DotPos)
if isnumeric(@test)=1
begin
set @RealNo = @test
return @RealNo
end
else
begin
set @RealNo = substring(@OriginalDocNo,
@DotPos + 1,
Len(@OriginalDocNo) - @DotPos - 1)
return @RealNo
end
end
return 99999
END
Part 2:
SELECT dbo.SortIt(DocNo, 'L') AS leftpart, dbo.SortIt(DocNo, 'R')
AS rightpart, dcs.*
FROM tbl_Documents dcs
ORDER BY leftpart, rightpart, DocNo
This returns all ascii values at the top of the recordset (alpha
sorted) and numbers like 1.11 after 1.9
Thanks all for the help
G
---
You are currently subscribed to cfaussie as:
[EMAIL PROTECTED]
To unsubscribe send a blank email to
[EMAIL PROTECTED]
Aussie Macromedia Developers: http://lists.daemon.com.au/
---
You are currently subscribed to cfaussie as: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]
Aussie Macromedia Developers: http://lists.daemon.com.au/
