StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><code>varchar</code>s and equality are thorny in TSQL. The <code>LEN</code> function says:</p> <blockquote> <p>Returns the number of characters, rather than the number of bytes, of the given string expression, <strong>excluding trailing blanks</strong>.</p> </blockquote> <p>You need to use <code>DATALENGTH</code> to get a true <code>byte</code> count of the data in question. If you have unicode data, note that the value you get in this situation will not be the same as the length of the text.</p> <pre><code>print(DATALENGTH(' ')) --1 print(LEN(' ')) --0 </code></pre> <p>When it comes to equality of expressions, the two strings are compared for equality like this:</p> <ul> <li>Get Shorter string</li> <li><strong>Pad with blanks</strong> until length equals that of longer string</li> <li>Compare the two</li> </ul> <p>It's the middle step that is causing unexpected results - after that step, you are effectively comparing whitespace against whitespace - hence they are seen to be equal.</p> <p><code>LIKE</code> behaves better than <code>=</code> in the "blanks" situation because it doesn't perform blank-padding on the pattern you were trying to match:</p> <pre><code>if '' = ' ' print 'eq' else print 'ne' </code></pre> <p>Will give <code>eq</code> while:</p> <pre><code>if '' LIKE ' ' print 'eq' else print 'ne' </code></pre> <p>Will give <code>ne</code></p> <p>Careful with <code>LIKE</code> though: it is not symmetrical: it treats trailing whitespace as significant in the pattern (RHS) but not the match expression (LHS). The following is taken from <a href="http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58433" rel="noreferrer">here</a>:</p> <pre><code>declare @Space nvarchar(10) declare @Space2 nvarchar(10) set @Space = '' set @Space2 = ' ' if @Space like @Space2 print '@Space Like @Space2' else print '@Space Not Like @Space2' if @Space2 like @Space print '@Space2 Like @Space' else print '@Space2 Not Like @Space' @Space Not Like @Space2 @Space2 Like @Space </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload