null/empty-string first last-CodePudding

In a lot of SQL dialects, they support the [NULLS {FIRST | LAST}]. Why isn't their also that same extension to the ORDER BY for sorting empty strings, which I often find myself wanting to push to the beginning or the end. For example, in the following column:

`name`
NULL
"tom"
"alex"
""
"zack"

I could see myself wanting to sort by something like:

NULL
"alex"
"tom"
"zack"
""

In which case it would nice to be able to do:

ORDER BY name NULLS FIRST EMPTY_STRING LAST

Or both of them at the end (nulls before empty string):

ORDER BY name NULLS LAST EMPTY_STRING LAST

Is there a construction like that in SQL? Or it only allows the NULL shortcut?

CodePudding user response：

For Sql server you can control the order with a xx_BIN collation. For example varchar column, NULLs first, empty strings last:

order by name   char(0xff) collate Latin1_General_Bin

CodePudding user response：

You could use NULLIF:

ORDER BY NULLIF(name, '') NULLS LAST

CodePudding user response：

SQL Server does not have an empty string last function. You can either cast empty strings as NULL or you will need to create a custom sort.

--This will treat blank values as NULLs
ORDER BY
    CASE
        WHEN name = '' THEN NULL
        ELSE name
        END NULLs LAST

--This custom order will put blanks first and nulls last
ORDER BY
    CASE
        WHEN name = '' THEN 1
        ELSE 2
        END
    ,name NULLs LAST