这个好想应该去sqlserver那边问问吧?是不是名字被并上空格了?举例:
在oracle中
char(8)字段和varchar2(8)字段保存相同字符串的结果却是不一样的char字段如果字符串长度不足则补空格'aaa'
char(8) varchar2(8)
--------------------------
'aaa ' 'aaa' 如果是char型,你用 field='aaa'是查不出来的
在oracle中
char(8)字段和varchar2(8)字段保存相同字符串的结果却是不一样的char字段如果字符串长度不足则补空格'aaa'
char(8) varchar2(8)
--------------------------
'aaa ' 'aaa' 如果是char型,你用 field='aaa'是查不出来的
把Oracle和SQL的表结构贴出来? 我觉得白开心说的是一个常见问题.很多据库之间的数据转移
都会遇见这个问题 作个测试,查询一下SQL Server 下name字段的长度.
select len(name) from tablename where name='张三'
select substring(name,1,len(name)) from tablename
where name='张三' select rtrim(name) from tablename where name='张三'
select ltrim(name) from tablename where name='张三'
select ltrim(rtrim(name)) from tablename where name='张三'
...
另外对name 为NULL的行也做一下测试.
不行呀!!55555。
To 楼上: 那就说明你的name列不是nvarchar或者nchar类型.
用我说的方法逐个测试一下吧.
Oracle中pat_master_index表的语句:
create table pat_master_index
(
patient_id varchar2(10),
inp_no varchar2(6),
name varchar2(8),
name_phonetic varchar2(16),
sex varchar2(4),
date_of_birth date,
birth_place varchar2(6),
citizenship varchar2(2),
nation varchar2(10),
id_no varchar2(18),
identity varchar2(10),
charge_type varchar2(8),
unit_in_contract varchar2(11),
mailing_address varchar2(40),
zip_code varchar2(6),
phone_number_home varchar2(16),
phone_number_business varchar2(16),
next_of_kin varchar2(8),
relationship varchar2(2),
next_of_kin_addr varchar2(40),
next_of_kin_zip_code varchar2(6),
next_of_kin_phone varchar2(16),
last_visit_date date,
vip_indicator number(1),
create_date date,
operator varchar2(8),
constraint pk_pat_master_index
primary key
(
patient_id
));
SqlServer2000中pat_master_index语句:
CREATE TABLE [dbo].[PAT_MASTER_INDEX] (
[PATIENT_ID] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[INP_NO] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[NAME] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[NAME_PHONETIC] [varchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[SEX] [varchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
[DATE_OF_BIRTH] [datetime] NULL ,
[BIRTH_PLACE] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[CITIZENSHIP] [varchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[NATION] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[ID_NO] [varchar] (18) COLLATE Chinese_PRC_CI_AS NULL ,
[IDENTITY] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[CHARGE_TYPE] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[UNIT_IN_CONTRACT] [varchar] (11) COLLATE Chinese_PRC_CI_AS NULL ,
[MAILING_ADDRESS] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
[ZIP_CODE] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[PHONE_NUMBER_HOME] [varchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[PHONE_NUMBER_BUSINESS] [varchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[NEXT_OF_KIN] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[RELATIONSHIP] [varchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[NEXT_OF_KIN_ADDR] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
[NEXT_OF_KIN_ZIP_CODE] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[NEXT_OF_KIN_PHONE] [varchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[LAST_VISIT_DATE] [datetime] NULL ,
[VIP_INDICATOR] [decimal](1, 0) NULL ,
[CREATE_DATE] [datetime] NULL ,
[OPERATOR] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
哦,我想原因可能出在SQL Server 的建表脚本上.
把那些 包含Chinese_PRC_CI的东西都去掉,你再做个测试?
我很奇怪为什么我用Sqlserver2000的DTS从Oracle中导出的数据也会有问题!!郁闷