If you sort a character number field (for example, 1-2)

Sorting problem Description: Often encountered in the development of the "1-2", "1-15" or "1.2", "1.15" data fields to sort, and such a field can not be directly by the way of ordering by. WORKAROUND: Make this data field output into a field that can be sorted directly by a user function: CREATE function dbo. Getnumberorderstring
(
@NumberString NVARCHAR (200),--characters to be processed
@SplitChar NVARCHAR (10) = '-',--separator
@Length INT = 2--Length of characters per segment
) RETURNS NVARCHAR () as
BEGIN
DECLARE @Result NVARCHAR (500)--results
SET @Result = '
DECLARE @F INT--the position of the first delimited character while LEN (@NumberString) > 0
BEGIN
--Remove the string
SET @F = CHARINDEX (@SplitChar, @NumberString)--Take the position of the first separator string
--print @F
DECLARE @v NVARCHAR (100)
IF @F = 0
BEGIN
SET @v = @NumberString
--print @v
SET @NumberString = '
--print @NumberString
End
ELSE
BEGIN

SET @v = SUBSTRING (@NumberString, 0, @F)--previous value of the first separator string
--print @v
SET @NumberString = SUBSTRING (@NumberString, @F + 1, LEN (@NumberString)-@F)--the value after the first separator string
--print @NumberString

End

--align strings by length
--IF LEN (@v) > @Length RAISERROR (' length of alignment out of range ', 1, 1)
IF IsNumeric (@v) = 1 SET @v = REPLICATE (' 0 ', @Length-len (@v)) + @v
ELSE SET @v = @v + REPLICATE (' 0 ', @Length-len (@v))
SET @Result = @Result + @v
End
Return @Result
End
Use instance: SELECT * from Item ORDER BY dbo. Getnumberorderstring (ID, '-', 3)