0

I have a SQL Server table with a varchar(30), not null. There is a record that appears to have an empty string value for that column, however the application that reads it out is triggering a FluentValidation error on a NotNull check. Looking at the data in SSMS by record id, it looks like it's an empty string, but when I search for where (column is null or column = '') there are no results. If I copy the value from that column and paste it in the where field = '', there's still no result.

Casting the varchar to varbinary, it shows a value of '0x0000000000000000000000000000000000000000'

If I copy the data from SSMS (by selecting the row in SSMS) and pasting it in Notepad++, with all symbols visible, it still shows no value for that column. Specifically, Notepad++ just shows two tabs that delimit between the values of the column before and after it.

Any idea what this special string could be? Is there a way to have a 0 length string that is not equal to ''?

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
cost
  • 4,420
  • 8
  • 48
  • 80
  • https://stackoverflow.com/questions/3891331/find-non-ascii-characters-in-varchar-columns-using-sql-server https://dba.stackexchange.com/questions/138350/how-to-check-for-non-ascii-characters – squillman Feb 16 '22 at 20:28

1 Answers1

3

What I see is a varchar value with a single \0 character. In many platforms the \0 is used to define the end of a string value (called the "null terminator"), yet strings in SQL Server are not null-terminated.

Therefore we have a value (it's not NULL) with a length of one byte (it's not empty/''), just as you have observed... but the byte in the string is not printable and is likely to be interpreted as not part of the string at all in many environments.

Check for this by using the LEN() function. I bet you see 1, rather than 0, as the result.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • It occurred to me to check the length (I was going to try DATALENGTH() instead of LEN()) shortly after I posted this, but the data is on a remote machine with a terrible connection and I hadn't been able to check it yet. However it makes me wonder, wouldn't a .net sql server client still read this out as a "zero length" null terminated string, and not as a null? I'm trying to find some docs about how sql server reading would act in that case – cost Feb 16 '22 at 20:53
  • That server outright died, but setting up a manual test I see it reads out a '\0' which triggers a combination of other random things in other code that is eventually resulting in it becoming null. Good ol null terminators, thanks! – cost Feb 16 '22 at 21:10
  • 1
    I would not expect the .Net sqlclient to read it as a 0-length string, because .Net strings are not null-terminated either. However, I've seen C# programs with bugs where a null terminator somehow made it into a string, then more text was appended, and the additional text was not visible or was lost in certain situations. Additionally, the _transport layer_ (tds) might be getting confused somewhere. – Joel Coehoorn Feb 16 '22 at 21:28