Re: Collation....

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

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 10/13/04 

Date: Wed, 13 Oct 2004 22:09:02 +0200

On Wed, 13 Oct 2004 09:39:30 -0700, John Smith wrote:

>When I run a query I want to differentiate between the data "E" and "E ".

Hi John,

When you use fixed-length columns (or variables), you can't. Store "E" in
a char(5) or nchar(5) and it will be padded with spaces to "E ". Use
the same datatype to store "E " and it will be padded to "E " as well.
There's no way to tell them apart.

If you use varying-length (varchar or nvarchar), trailing spaces will be
retained. However, on comparison the shorter string will be padded to
match the length of the other string. So while "E " and "E" are stored
exactly as this, the shorter ("E") is still padded to "E " before the
comparison is made.

The workaround is to concatenate something in the comparison. Instead of
using "WHERE Col01 = Col02", you use "WHERE Col01 + 'Q' = Col02 + 'Q'".
This will change the strings to be compared to "E Q" and "EQ"; after
padding the shorter one to match the other one's length, they'll still be
different ("E Q" vs "EQ ").

The downside of this is that an index on Col01 or Col02 (if it exists) can
now no longer be used for the query.

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: Encode combination efficiently
    ... wrong the average bits/element will be around 12.54 bits/element with ... could end the string out in at least two different ways. ... shorter strings to make them all the same length. ... I know you have a hatred of bijective compression but this ...
    (comp.compression)
  • Re: varchar problem
    ... A space is just another character. ... >> want to strip them, ... VARCHAR strings will only be as long as ... > the stored string itself. ...
    (alt.php)
  • RE: ADO.NET 2.0 saving single space to SQL?
    ... It turns out that changing the parameter to a type of varchar or using the ... Fields that are being inserted into the database as a zero length ... string in 1.1 contain a single space when inserted with 2.0 compiled code. ... The code specifies a stored proc to run and set up the parameter collection. ...
    (microsoft.public.dotnet.framework.adonet)
  • Not getting all data from Stored Procedure.
    ... I am trying to get the identity of a row I add in my stored procedure. ... Dim ConnectionString as String ... @srvname varchar, ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: 1st and 2nd.... positions
    ... It took your posting the idea of searching a string for the correct position to give me a nudge. ... Can the shorter main text string in the second formula really add significantly to the efficiency of performing the MID function call that it can compensate for the extra function call? ...
    (microsoft.public.excel.misc)