Re: Comportament varchar/char

rsocol — Tue, 09 Jan 2007 07:32:43 GMT

Comportamentul SQL Server la compararea unor expresii de tip caracter este documentat la: http://msdn2.microsoft.com/en-us/ms189863.aspx

În această pagină se face referire şi la standardul SQL-92. Un draft aproape final al acestui standard e disponibil la adresa: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt (standardul oficial e disponibil contra cost de la ISO sau ANSI).

După câte înţeleg eu, situaţia e puţin invers: nu se scot spaţiile, ci se adaugă. Vezi punctul 3) din secţiunea 8.2 <comparison predicate> a standardului:

            The comparison of two character strings is determined as follows:

            a) If the length in characters of X is not equal to the length
              in characters of Y, then the shorter string is effectively
              replaced, for the purposes of comparison, with a copy of
              itself that has been extended to the length of the longer
              string by concatenation on the right of one or more pad char-
              acters, where the pad character is chosen based on CS. If
              CS has the NO PAD attribute, then the pad character is an
              implementation-dependent character different from any char-
              acter in the character set of X and Y that collates less
              than any string under CS. Otherwise, the pad character is a
              <space>.

            b) The result of the comparison of X and Y is given by the col-
              lating sequence CS.

            c) Depending on the collating sequence, two strings may com-
              pare as equal even if they are of different lengths or con-
              tain different sequences of characters. When the operations
              MAX, MIN, DISTINCT, references to a grouping column, and the
              UNION, EXCEPT, and INTERSECT operators refer to character
              strings, the specific value selected by these operations from
              a set of such equal values is implementation-dependent.

Răzvan

Comportament varchar/char

lmsasu — Tue, 09 Jan 2007 06:35:49 GMT

Salut,

am o tabela creata astfel:

CREATE TABLE [dbo].[Test](
   [id] [int] IDENTITY(1,1) NOT NULL,
   [nume] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
   [prenume] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
   [id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Inserez in tabela:

insert into Test (nume, prenume) values ('Popescu', 'Ana')

Caut:

select nume, prenume from test where nume='Popescu '
select nume, prenume from test where prenume='Ana '

Remarcati spatiul de la sfarsitul stringurilor, inainte de apostroful de inchidere.

Rezultat: de fiecare data imi returneaza inregistrarea inserata, chiar daca valorile puse intial sunt fara spatii la sfarsit; treaba se intampla atat pentru nchar cat si pentru nvarchar. De ce? Operatorul egal face cumva si trimming, automat? Care e explicatia? S-ar putea sa fie si un raspuns de tipul RTFM, dar dati o referinta :)

Lucian

Diverse

Re: Comportament varchar/char

Comportament varchar/char