none
I have columns in my database that are zero length but not null RRS feed

  • Question

  • I am not sure that is in the columns, but they are not null and have zero length, I have tried the following

    and(data_value.string_valueISNOTNULL

    ORLEN(data_value.string_value)>0

    ORNULLIF(LTRIM(RTRIM(data_value.string_value)),'')ISNOTNULL

    ordata_value.string_value<>''

    orCOALESCE(data_value.string_value,'')<>''

    or  COALESCE(PATINDEX('%[^ ]%',data_value.string_value),0)>0

    orNULLIF(LTRIM(RTRIM(data_value.string_value)),'')ISNOTNULL)

    I displayed len(data_value.string_value) and it is zero

    I copied the column into notepad++ and clicked show all values that it showed nothing

    Any help understanding what is in these columns would be appreciated

    Thank you in advance

     

      

    Saturday, February 9, 2019 2:02 PM

All replies

  • I displayed len(data_value.string_value) and it is zero  

    The LEN function ignores trailing spaces so the zero value indicates it is either an empty string or all spaces. A DATALENGTH(data_value.string_value) value of zero indicates an empty string, which is a valid value in SQL Server. Some other DBMS products do not allow empty strings.

    If DATALENGTH returns a non-zero value, it may be whitespace or non-displayable value. In T-SQL, you can use CAST(data_value.string_value AS varbinary(MAX)) to see the bits as stored in the database.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    Saturday, February 9, 2019 3:13 PM
  • thank you, using datalength works

    Monday, February 11, 2019 2:56 PM
  • thank you, using datalength works

    Since you have got your answer, please kindly close the thread by marking useful reply as answer.

    Thanks for your contribution.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 12, 2019 6:57 AM
    Moderator