the method of dealing with space in field is not good
-
I think the method of dealing with space in field is not good
CREATE TABLE [T2] (
[C1] [int] NOT NULL ,
[C2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_T2] PRIMARY KEY CLUSTERED
(
[C1]
 
ON [PRIMARY]) ON [PRIMARY]
GO
insert into t2 values(1,''); --no space
insert into t2 values(2,' '); --one space
insert into t2 values(3,' ');--two space
select * from t2 where c2=''; --no space
select * from t2 where c2=' '; --one space
select * from t2 where c2=' '; --two space
The SELECT statements return the same records. What is wrong with SQL-server?
-
Check the inserted lines... Once on the server they are all kept as '' (no space). Looks like the where condition is treated the same way (rtrimed). As I remember it, access is dealing with the right padded spaces the same way that sql is treating 'em... Maybe somebody else can elaborate more than me on that subject.
-
The answer to your question can be found in Books Online. Search for this topic, 'Using char and varchar Data'. Basically, it states that the data type varchar truncates all trailing spacesp provided the the following setting 'ANSI_PADDING' is turned off. To keep the spaces, you will need to turn this setting on.
Dave N
-
Dave, I think you are right. I checked the information you hinted me. It is helpful. But the database reponses no change after I modified the database option. Here is my code. Before execuing it, I restarted the database.
execute sp_dboption test3,ANSI_PADDING
drop table t4
CREATE TABLE [dbo].[t4] (
[c1] [int] NULL ,
[c2] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
delete from t4
insert into t4 values(1,''); --no space
insert into t4 values(2,' '); --one space
insert into t4 values(3,' ');--two space
select * from t4 where c2=''; --no space
select * from t4 where c2=' '; --one space
select * from t4 where c2=' '; --two space
Here is the output:
OptionName CurrentSetting
----------------------------------- --------------
ANSI padding ON
(0 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
c1 c2
----------- --------------------------------------------------
1
2
3
(3 row(s) affected)
c1 c2
----------- --------------------------------------------------
1
2
3
(3 row(s) affected)
c1 c2
----------- --------------------------------------------------
1
2
3
(3 row(s) affected)
The reason why I have to emphasize the issue is that the odd behavior of space makes me frustrated during analyzing the string field in a table, separating string with space delimiter, using SQL function to compare them.
-
That's why most people use a comma as a delimiter. Looks like you're gonna have to modify your system a little bit to avoid this problem.
-
It makes me feel bad since I have to change my system to adapt some database defects, if there are. I use Oracle to development for quite a while. I don not think I will encounter this problem in Oracle. I do meet some problems in Sql-server, especially the concurrent access. You know, even I am going to update a single row in a table that is not indexed; Sql-server will lock the whole table. Sometimes, when I do some DTS, using Import and Export Tool, it locks some tables and never releases them. In some cases, I cannot operate the table for unknown reason; I have to create a new table to replace it in my program.
I hesitate to say so because I am not a Sql Server expert. I want to say so because it wastes a lot of my time.
-
Learning a db software is never a waste of time.. it's just a learning curve.
-
People believe that just because SQL Server is easy to install it is also easy to use, and then blame all their problems on SQL Server being a lousy product. Noone blames Oracle for executing a query slowly, instead they start changing the values of a zillion configuration options.
The ANSI SQL standard says the when comparing strings with the = operator the strings should be right-trimmed. So SQL Server is only following the standard.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply