Your sample data was ambiguous and misleading. If you really just want to order by whatever column isn’t '', and given your assurance that no columns are NULL and there are no ' ' etc., you can use CONCAT, which will simply munge whatever value is not empty with two empty strings:
SELECT FirstName, LastName, MiddleName
FROM dbo.t
ORDER BY CONCAT(firstname, lastname, middlename);
Output:
| FirstName | LastName | MiddleName |
|---|---|---|
| dave | ||
| jeff | ||
| matt | ||
| Mccafrey | ||
| tom | ||
| TomBrady | ||
| zedaya |
- example db<>fiddle
0
solved How to order by multiple columns with same datatype [closed]