none
What is the difference between '' and ' ' in SQL Server RRS feed

  • Question

  • So I see T-SQL sometimes with a place-holder as SELECT ''(no <blank> space) and sometimes with ' '(one <blank> space).

    Is there any difference between the two and how that is handled in extracts and the like or is it merely just a developer preference?

    Thanks for your review and am hopeful for a reply.

    Friday, January 25, 2019 1:55 PM

All replies

  • So I see T-SQL sometimes with a place-holder as SELECT ''(no <blank> space) and sometimes with ' '(one <blank> space).

    Is there any difference between the two and how that is handled in extracts and the like or is it merely just a developer preference?

    Thanks for your review and am hopeful for a reply.

    Good day ,

    >> Is there any difference between the two

    Obviously there is. You told us the different: If you select ' ' then you get ' ' while if you select '' then you get ''

    Your question is totally not clear to me. You can also select 'a' and get 'a' for that matter

    select 
    	' ' as [here you have space in the result] , 
    	'' as [here you DON'T have space in the result] 
    GO


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    Friday, January 25, 2019 6:58 PM
    Moderator
  •   Select LEN(''), LEN(' ')-- Even though both have a length of 0, In strings ' ' it is considered as one space character where as '' is empty string
      Select SQL_VARIANT_PROPERTY('','BaseType') ,SQL_VARIANT_PROPERTY(' ','BaseType') --Both are Varchar datatype

    mohammad waheed

    Friday, January 25, 2019 7:09 PM
  •   Select LEN(''), LEN(' ')-- Even though both have a length of 0, In strings ' ' it is considered as one space character where as '' is empty string
      Select SQL_VARIANT_PROPERTY('','BaseType') ,SQL_VARIANT_PROPERTY(' ','BaseType') --Both are Varchar datatype

    mohammad waheed

    This is really a bad example. Check the Documentation regarding the function LEN:

    "Returns the number of characters of the specified string expression, excluding trailing blanks."

    By definition the LEN function does not count the blank space in your example

    You should use DATALENGTH instead and then you will get the right answer that ' ' has length or 1 while '' has length of 0 

    Select DATALENGTH(''), DATALENGTH(' ')


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    Friday, January 25, 2019 7:41 PM
    Moderator