4

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:

enter image description here

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.

Thom A
  • 88,727
  • 11
  • 45
  • 75
gotqn
  • 42,737
  • 46
  • 157
  • 243
  • What collation are you using? Latin collations, for example, don't "play nice" with non-Latin alphabets. – Thom A Aug 30 '23 at 11:31
  • The original statement is referring column with `SQL_Latin1_General_CP1_CI_AS`. – gotqn Aug 30 '23 at 11:32
  • 1
    That's likely the route problem then. Using a Khmer collation, for example, doesn't replicate the issue: `SELECT NULLIF(N'ធ្វើឱ្យ' COLLATE Khmer_100_CI_AI, '')` – Thom A Aug 30 '23 at 11:33
  • @ThomA it's working yes, but I may have different languages in the column, so I can't specified it. It seams I need to change the collation to solve this to something else, which I guess will be hard, as I can get `cannot resolve the collation conflict` error on other places. Or to change the code to for NULL-ing empty string, which is again bad because I have used this syntax a lot :-) – gotqn Aug 30 '23 at 11:38

1 Answers1

3

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
Thom A
  • 88,727
  • 11
  • 45
  • 75