Problem Description:
The data fields to "1-2", "1-15" or "1.2", "1.15" are often encountered in the development, and such fields cannot be sorted by the order by ORDER BY.
Solution:
By a user function, this type of data field becomes output into a field that can be sorted directly:
Create function dbo.getNumberRDERSTRING
(
@Numberstring nvarchar (200), - to be processed characters
@Splitchar nvarchar (10) = '-', - separator
@Length INT = 2 - Character length per segment
)
Returns nvarchar (500) AS
Begin
Declare @Result nvarchar (500) - Result
Set @Result = ''
Declare @f int - the location of the first divided character
While Len (@numberstring)> 0
Begin
- Remove string
Set @f = charindex (@splitchar, @numberstring) - Take the first partition of the location
--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) - Before taken the first partition string
--Print @V
Set @numberstring = Substring (@numberstring, @f 1, len (@numberstring) - @f) - Take the value after the first partition string
--Print @numberstring
End
- Aligned string by length
- if len (@v)> @Length Raiserror ('aligned length beyond 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 example:
Select * from item order by dbo.getnumberorderstring (ID, '-', 3)
