NULLIF/IIF returning undesired results when comparing Khmer to zero length string

I have find a T-SQL statement failing to make a string NULL, if it's empty string. It's like:

SELECT NULLIF(N'ធ្វើឱ្យ', '') -- NULL

According to google translator the word means "make" on Khmer language. If I try:

SELECT IIF(N'ធ្វើឱ្យ' = '', 1, 0) -- 1

So, the engine says this string is empty.

I am not sure if this is a support language issue, as here I have:

or some type data type precedence casting as we have two strings. It's strange how then engine is not able to understand the string is not empty?

After all the SELECT LEN(N'ធ្វើឱ្យ') gives me seven, just wondering what's the exact reason.

I assume that the NULLIF is in your SELECT and this is for presentation or final consumption, rather than in something like the WHERE or an ON (if it is, this is likely an XY Problem).

As mentioned in the comments, Latin collations don't "play nice" with non-Latin alphabets (or emoticons). As a result the comparison of N'ធ្វើឱ្យ' and '' is resulting in them being treated as equal even though they clearly aren't.

If you were solely working with Khmer text I would have suggested you change the collation to a Khmer colation (such as Khmer_100_CI_AI), however, as you have many languages a binary collation would likely be better™. Then, if needed, you can COLLATE the value back to the database default:

SELECT NULLIF(N'ធ្វើឱ្យ' COLLATE Latin1_General_BIN, '') COLLATE DATABASE_DEFAULT; --ធ្វើឱ្យ

SELECT IIF(N'ធ្វើឱ្យ' COLLATE Latin1_General_BIN = '', 1, 0) -- 0