Is not empty string not working for special characters

Asked Jan 12 '18 at 13:53

Active Jan 12 '18 at 13:56

Viewed 108 times

I recently find out that the check I am using for empty string is not working for some of the data. I am using:

[DataField] <> ''

And for some characters it seems to not work:

For example, the following yields 1:

SELECT IIF(N'' = '', 1, 0);

It seems, that the ASCII code for all of this is 63:

SELECT ASCII(N'');
SELECT ASCII(N'');
SELECT ASCII(N'');
SELECT ASCII(N'');
SELECT ASCII(N'');
SELECT ASCII(N'☕');

In the table it is ?.

If I use, CHAR(63) the check is working:

SELECT IIF(CHAR(63) <> '', 1, 0); -- 1
SELECT IIF(N'' <> '', 1, 0);    -- 0

Of course, I just change the check to be LEN([DataField]) > 0 and now everything is fine. Just wondering, can anyone explain, why the initial check fails?

asked Jan 12 '18 at 13:53

gotqn

42,737
46
157
243

Emojis are Unicode and yet you put them inside ASCII strings. Add the N prefix – Panagiotis Kanavos Jan 12 '18 at 13:58
1

lol - love how even the link here prints the emojis – Tanner Jan 12 '18 at 14:01
@PanagiotisKanavos Xax, yes, thanks. – gotqn Jan 12 '18 at 14:02
NB: From the documentation for [`Len()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/len-transact-sql): "Returns the number of characters of the specified string expression, **excluding trailing blanks**." – HABO Jan 12 '18 at 22:28

Is not empty string not working for special characters

0 Answers0