StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><a href="http://support.microsoft.com/kb/316626" rel="noreferrer">Trailing blanks explained</a>:</p> <blockquote> <p>SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, , General rules #3) on how to <strong>compare strings</strong> with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings 'abc' and 'abc ' to be equivalent for most comparison operations.</p> <p>The only exception to this rule is the LIKE predicate. When the right side of a LIKE predicate expression features a value with a trailing space, SQL Server does not pad the two values to the same length before the comparison occurs. Because the purpose of the LIKE predicate, by definition, is to facilitate pattern searches rather than simple string equality tests, this does not violate the section of the ANSI SQL-92 specification mentioned earlier.</p> </blockquote> <p>Here's a well known example of all the cases mentioned above:</p> <pre><code>DECLARE @a VARCHAR(10) DECLARE @b varchar(10) SET @a = '1' SET @b = '1 ' --with trailing blank SELECT 1 WHERE @a = @b AND @a NOT LIKE @b AND @b LIKE @a </code></pre> <p>Here's some more detail about <a href="http://msdn.microsoft.com/en-us/library/ms179859.aspx" rel="noreferrer">trailing blanks and the <code>LIKE</code> clause</a>.</p> <p><strong>Regarding indexes:</strong> </p> <blockquote> <p>An insertion into a column whose values must be unique will fail if you supply a value that is differentiated from existing values by trailing spaces only. The following strings will all be considered equivalent by a unique constraint, primary key, or unique index. Likewise, if you have an existing table with the data below and try to add a unique restriction, it will fail because the values are considered identical.</p> <pre><code>PaddedColumn ------------ 'abc' 'abc ' 'abc ' 'abc ' </code></pre> </blockquote> <p>(Taken from <a href="http://support.microsoft.com/kb/231830/EN-US" rel="noreferrer">here</a>.)</p>

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