RE: Collation....

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Bart Duncan [MSFT] (bartd_at_online.microsoft.com)
Date: 10/13/04 

Date: Wed, 13 Oct 2004 17:09:23 GMT

ANSI SQL-92 suggests that two strings which differ only in trailing spaces
should be considered equal. In SQL Server, with most collations a simple
'=' operator complies with this. In SQL there are only two exceptions to
this that I know of:
 - Use 'LIKE' instead of '='. LIKE considers trailing spaces in the search
criteria (right operand) to be significant. However, trailing blanks in
the column to be searched (left operand) are still ignored. See the
Remarks section of the "LIKE" topic in Books Online for details and some
examples.
 - Use a binary collation such as SQL_Latin1_General_BIN. Note, however,
that this will treat all code points as different characters. For example,
'e' will be considered a different string than 'E'.

Bart
------------
Bart Duncan
Microsoft SQL Server Support

Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| From: "John Smith" <JSmith_67@hotmail.com>
| Subject: Collation....
| Date: Wed, 13 Oct 2004 09:39:30 -0700
| Lines: 7
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
| Message-ID: <#9iatNUsEHA.3012@TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.programming
| NNTP-Posting-Host: 64.173.130.210
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP0
8.phx.gbl!TK2MSFTNGP10.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.programming:478182
| X-Tomcat-NG: microsoft.public.sqlserver.programming
|
| I am using SQL Server 2000.
| When I run a query I want to differentiate between the data "E" and "E ".
| How to differentiate the data which has blank space after it and which
does
| not have blank space after it? Thanks for your answers.
| Smith
|
|
|

Relevant Pages

  • Re: Installing SQL_Latin1_General_CP1_CI_AS collation order??
    ... collations, where a collation controls things as the character repertoire, ... Tibor Karaszi, SQL Server MVP ... "Ward Horsfall" wrote in message ...
    (microsoft.public.sqlserver.setup)
  • Re: SYSTEM_USER automatic RTRIM of trailing spaces in 2000, but not 2005?
    ... use a login with trailing spaces on the name, ... automatically trims those trailing spaces in SQL Server 2000, ...
    (comp.databases.ms-sqlserver)
  • Re: Replication Monitor didnt work because different collations
    ... Looking for a SQL Server replication book? ... SQL 2005 filtered merge replication. ... We changed collations to match the server collation and Replication ... After this change we started all publication snapshots. ...
    (microsoft.public.sqlserver.replication)
  • Re: Better "Join" vs "Where" clause?
    ... Yes, MS SQL Server does it accordingly to the ANSI specs, while JET does ... While MS SQL Server and Jet indeed differ on this statement, ... I'm no expert on ANSI, so I'm not sure which is the behavior specified by ...
    (microsoft.public.access.queries)
  • Re: Cannot trim data
    ... I had make a test in my SQL Server, and I found that it happens in this ... > I have examine the data today and they are still contain trailing spaces. ... >> so to speak, in the current collation. ... >> Steve Kass ...
    (microsoft.public.sqlserver.programming)