There is a table containing a field of type sql_variant . In all entries in the table, the field is not NULL . Checking for an empty string does nothing. The LEN() function does not accept fields of type sql_variant as a parameter.

The table contains attributes of arbitrary data types, so it is impossible to determine by which type of data is expected there by the type of the empty field in the query results, it can be anything. NULL values ​​are always shown explicitly, there is no problem with them. The problem itself with a direct request like:

 select * from tbl where variantField <> ''

Even under the condition that there are supposedly lines in the records that interest me.

How else can you check that this field is empty, provided that it is not NULL ?

Search for EnSO has given nothing yet, although it may have been looking badly

UPD It looks like it was a server glitch, the same script that worked wrong yesterday, worked without problems with the expected results

    1 answer 1

    Not quite clear what it means

    the field is empty provided it is not NULL

    I think that it is necessary to proceed from what data is stored in this field, and what to take for "empty . "

    See it. Suppose, for example, there is data:

     declare @varData table (id int, value sql_variant); insert into @varData values (1, 1); insert into @varData values (2, 'AAAA'); insert into @varData values (3, 0.26); insert into @varData values (4, 0x); insert into @varData values (5, getdate()); insert into @varData values (6, ''); insert into @varData values (7, newid()); insert into @varData values (8, N' '); insert into @varData values (9, 0x0000);

    If we mean "empty, but not NULL" in the sense that the length of an element is 0 bytes, then the function DATALENGTH is the closest in meaning. Request

     select * from @varData where datalength(value) = 0

    returns rows with id = 4 and id = 6 .

    Strings whose field value is empty (in the ANSI-SQL sense) string will return the query

     select * from @varData where value = ''

    In this case, the row with id = 4 not returned (although the value of the value field is empty in the sense of the datalength criterion), but the rows with id = 6 returned (which is logical) and id = 8 (which is logical from the ANSI-SQL point of view, but can to be somewhat unexpected from the point of view, for example, C #, where the same string is not empty, although composed of white-space characters).

    Lines whose field value is empty (again in the ANSI-SQL sense) sequence of bytes will return the query

     select * from @varData where value = 0x

    In this case, the string with id = 6 is not returned (although its value empty in the sense of the datalength criterion), and the rows are returned with id = 4 (which is logical) and id = 9 (which may again be somewhat unexpected from the point of view of C #, where 0x0000 byte array is not empty at all).

    In general, choose a criterion based on what is required by the task and according to the data.

    ps but in general for void it is better to use NULL

    • about NULL for emptiness - I completely agree, but there’s really something to do, so you have to work. The probability of adjusting the database is close to zero, too much is already tied to it. And thanks for DATALENGTH , tomorrow I will try. - rdorn