Re: Cannot trim data
From: Stanley (some_guy_at_hongkong.com)
Date: 05/22/04
- Next message: ElanKathir .S.N: "Re: How to take Database Backup through the SQL Commands...."
- Previous message: Stanley: "Re: Cannot trim data"
- In reply to: Stanley: "Re: Cannot trim data"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 22 May 2004 10:54:10 +0800
I had make a test in my SQL Server, and I found that it happens in this
table only.
"Stanley" <some_guy@hongkong.com> 撰寫於郵件新聞
:e2P#PP6PEHA.2132@TK2MSFTNGP11.phx.gbl...
> Hi Steve,
>
> Thank you for your information, but the data copy form SQL Server
> is actually like this 'A0011* ' instead of 'A0011*',
> may be this news server trim the trailing space so you view my copied
result
> doesn't contain those trailing spaces.
> The result should be like this:
>
> SELECT RTRIM(LTRIM([Field1])), [Field1]
> FROM [Table1]
> WHERE [Field1] = 'A0011* ' OR
> [Field1] = 'A0012* '
>
> 'A0011*', 'A0011* '
> 'A0012*', 'A0012* '
>
> I have examine the data today and they are still contain trailing spaces.
>
>
> "Steve Kass" <skass@drew.edu> 撰寫於郵件新聞
> :eY6#7UuPEHA.3088@tk2msftngp13.phx.gbl...
> > Stanley,
> >
> > In SQL Server, string1 = string2 is always true if the only difference
> > between string1 and string2 is trailing spaces.
> >
> > After the update, the strings have been trimmed, but the trimmed
> > values still satisfy the where condition
> >
> > WHERE [Field1] = 'A0011* '
> >
> > The = operator doesn't test whether the strings contain the exact same
> > sequence of characters, only that they are considered the same "word",
> > so to speak, in the current collation.
> >
> > If you need to distinguish between 'A0011*' and 'A0011* ', a string
> > type will not be sufficient. Here are some possibilities: use two
> > columns to model each value, the one you have along with another
> > containing the length; create a computed column that appends a non-blank
> > character; use a single column of type varbinary instead of nvarchar.
> > You can also use
> >
> > WHERE [Field1] + '*' = 'A0011* ' + '*'
> >
> > but it will probably be slower.
> >
> > Steve Kass
> > Drew University
> >
> >
> > Stanley wrote:
> >
> > >Hi Aaron,
> > >
> > >Please take a look of my SQLs
> > >
> > >SELECT RTRIM(LTRIM([Field1])), [Field1]
> > >FROM [Table1]
> > >WHERE [Field1] = 'A0011* ' OR
> > > [Field1] = 'A0012* '
> > >
> > >Result copy from [SQL Enterprise Manager]:
> > > A0011* A0011*
> > > A0012* A0012*
> > >
> > >Then run update statement
> > >
> > >UPDATE [Table1]
> > >SET [Field1] = RTRIM(LTRIM([Field1]))
> > >WHERE [Field1] = 'A0011* ' OR
> > > [Field1] = 'A0012* '
> > >
> > >two row(s) afftected.
> > >
> > >Then run again the select statement
> > >
> > >SELECT RTRIM(LTRIM([Field1])), [Field1]
> > >FROM [Table1]
> > >WHERE [Field1] = 'A0011* ' OR
> > > [Field1] = 'A0012* '
> > >
> > >Result copy from [SQL Enterprise Manager]:
> > > A0011* A0011*
> > > A0012* A0012*
> > >
> > >So I think there is trailing spaces cannot be trimmed
> > >Please be mentioned that I am using SQL Server 7.0
> > >I tried delete the trailing spaces one by one, it works fine, but
spaces
> > >cannot be trimmed in the update statement.
> > >Thank you for you patient to read this long message.
> > >
> > >
> > >
> > >
> >
>
>
- Next message: ElanKathir .S.N: "Re: How to take Database Backup through the SQL Commands...."
- Previous message: Stanley: "Re: Cannot trim data"
- In reply to: Stanley: "Re: Cannot trim data"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
