CREATE TABLE #varchar_field1 ( ID INT IDENTITY(1,1), mixed_field VARCHAR(100) )

INSERT INTO #varchar_field1 (mixed_field) 
SELECT '1' UNION ALL SELECT '4.9' UNION ALL SELECT '10' 
UNION ALL SELECT '50' UNION ALL SELECT '6' UNION ALL SELECT 'a' 
UNION ALL SELECT 'z' UNION ALL SELECT 'A' 

SELECT *
FROM #varchar_field1
ORDER BY CASE WHEN PATINDEX('[a-zA-Z]', mixed_field) > 0
        THEN mixed_field
        ELSE '' END
    ,CASE WHEN PATINDEX('[a-zA-Z]', mixed_field) > 0 
        THEN 0
        ELSE CAST(mixed_field AS DECIMAL(10,1)) END

DROP TABLE #varchar_field1

CREATE TABLE #varchar_field1
    (ID INT IDENTITY(1, 1),
     mixed_field VARCHAR(100));

SET IDENTITY_INSERT #varchar_field1 ON;

INSERT  INTO #varchar_field1
        (ID, mixed_field)
VALUES  (1, '1'),
        (2, '4.9'),
        (3, '10'),
        (4, '50'),
        (5, '6'),
        (6, 'a'),
        (7, 'z'),
        (8, 'A');

SET IDENTITY_INSERT #varchar_field1 OFF;

SELECT  *
FROM    #varchar_field1
ORDER BY ID;

IF OBJECT_ID(N'tempdb..#varchar_field1') IS NOT NULL 
    DROP TABLE #varchar_field1;
	
CREATE TABLE #varchar_field1
    (Sequence INT,
     mixed_field VARCHAR(100));

INSERT  INTO #varchar_field1
        (Sequence, mixed_field)
VALUES  (1, '1'),
        (2, '4.9'),
        (3, '10'),
        (4, '50'),
        (5, '6'),
        (6, 'a'),
        (7, 'z'),
        (8, 'A');

SELECT  *
FROM    #varchar_field1
ORDER BY Sequence;