I was browsing the data for a particular column which looked like it had spaces because it did not say null
Upon trying to find what it was, I discovered the data does not have a single space but it is also not blank.
What is it then? -- something between two single quotations '' which is not space.
Tried to run this query with no results:
select column1 from table1 where column1 like '% %'
-- Single space
-> No records found
The second query I tried (see below) has no spaces mentioned. And that gave a result saying that there exist records which are like ->''
select column1 from table1 where column1 like '% %'
select column1 from table1 where column1= ''
I am trying to understand what is that record and what does it mean to be nothing?
What is it then? -- something between two single quotations '' which is not space.
It's a string of zero length. You're right, it's not a space - a space is ' ' - it has a length of one character. A string of length 0 is nothing like a string of length 1, no matter what character is in the string.
In any computer system we arbitrarily decide on the meaning of everything. Suppose you have a question that has an answer A, B, or C. You might also have situations where the person doesn't know, or knows but doesn't want to say. You might arbitrarily decide to have NULL means "don't know" and 0 length string as "don't want to say" in addition to all the people who do answer with a letter
This is an arbitrary decision, maybe you decide to store a space for "don't want to say", maybe an exclamation mark- whatever. Some database vendors think it is important to provide support for having strings of zero length, others (oracle) do not (oracle treats null and '' as the same)
Ultimately if your db supports zero length strings you have to understand what they are and in the context of your application you have to understand what they mean. What they are is like a glass with no water in it, or a bag of sweets with no sweets in it.. The statement of "there is no data" is different to "we don't know if there is any data or not" - the uncertainty here is more often associated with NULL
I am trying to understand what is that record and what does it mean to be nothing?
That record is a record with a zero length string in that column. However, we cannot tell you what it means because the decision to store a zero length string in it was taken by you or one of the other developers at your company, or a previous developer who quit. It could be that he wanted to record a difference between null and zero data. It could be that he couldn't be bothered checking if the data was null and wanted to avoid a typical null reference crash on the front end app. It could be that he wanted to count the data even though there was no data to put there
We cannot answer this part of your question
We can only tell you that zero length strings are possible, you can attach as much meaning to them as any other string, and they will never be equal to a string of non zero length so like '% %' will never find them. Do not assume that something you cannot see must be a space..