Server SQL sort by size

The following method uses the SQL function stored procedure to sort the size.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter function [dbo]. [udf_OrderLimitSize]
(
@ Ov NVARCHAR (30)
)
RETURNS decimal (18,6)
AS
BEGIN
-- If the length is less than or equal to 2, NULL is returned.
IF (LEN (@ Ov) <= 2)
RETURN NULL
-- Declare two variables
DECLARE @ v DECIMAL (18,6), @ n DECIMAL (18,6)

-- Determine whether the last two digits are the following units
IF (RIGHT (@ Ov, 2) not in ('TB', 'GB', 'mb', 'KB '))
RETURN NULL
-- After removing the last two digits, convert the value to the DECIMAL data type.
SET @ n = CONVERT (DECIMAL (18,6), LEFT (@ Ov, LEN (@ Ov)-2 ))
-- After the last two digits are intercepted, ISNUMERIC is used to determine whether the value is valid. If not, NULL is returned.
IF (ISNUMERIC (@ n) = 0)
RETURN NULL
-- Perform unit calculation. If a new unit exists, add it accordingly.
IF (@ Ov LIKE '% TB ')
SET @ v = @ n * 1024*1024*1024
IF (@ Ov LIKE '% G ')
SET @ v = @ n x 1024*1024
IF (@ Ov LIKE '% MB ')
SET @ v = @ n * 1024
IF (@ Ov LIKE '% kb ')
SET @ v = @ n
RETURN @ v
END

Instance

 

Create table test (id int identity (1, 1), size NVARCHAR (50 ))
GO
Insert into [test] values ('23. 5mb'), ('10gb'), ('12. 7mb'), ('8gb ')
Go
SELECT [id], [size] FROM test order by [dbo]. [udf_OrderLimitSize] ([size])