用了ltrim()、rtrim()去除左右空格之后,以下表达式 ltrim(rtrim(cast(bz_code as char(21))))as bz_code 返回的数据长度就不一定是21了,而是实际bz_code的长度
select
ltrim(rtrim(cast(bz_code as char(21))))as bz_code,--21 ltrim(rtrim(cast(czrq as char(11))))as czrq,--11 ltrim(rtrim(cast(zy_num as char(5))))as zy_num,--5 ltrim(rtrim(cast(jsdh as char(21))))as jsdh,--21 ltrim(rtrim(cast(keshi as char(41))))as keshi,--41 ltrim(rtrim(cast(price as char(1))))as price,---0 ltrim(rtrim(cast(dept_name as char(1))))as dept_name,---0 ltrim(rtrim(cast(doctor as char(1))))as doctor,---0 ltrim(rtrim(cast(fsrq as char(1))))as fsrq---0 From his..miHospTemp
where bz_code=''and czrq is not null and zy_num is not null and jsdh is not null and keshi is not null and bz_code is not null如果我要返回的结果是 21+11+5+21+41=98??,上面大语句改如何写呢^_^
把UNION ALL两端联合的两个select查询所返回的字段都用cast(col as char(n))转换,而且输出在同一列的数据长度要设置为一致。
select cast(ltrim(rtrim(cast(bz_code as char(21)))) as char(21)) as bz_code,--21 cast(ltrim(rtrim(cast(czrq as char(11)))) as char(11)) as czrq,--11 cast(ltrim(rtrim(cast(zy_num as char(5)))) as char(5) ) as zy_num,--5 cast(ltrim(rtrim(cast(jsdh as char(21)))) as char(21)) as jsdh,--21 cast(ltrim(rtrim(cast(keshi as char(41)))) as char(41)) as keshi,--41 cast(ltrim(rtrim(cast(price as char(1)))) as char(1) ) as price,---0 cast(ltrim(rtrim(cast(dept_name as char(1)))) as char(1) ) as dept_name,---0 cast(ltrim(rtrim(cast(doctor as char(1)))) as char(1) ) as doctor, ---0 cast(ltrim(rtrim(cast(fsrq as char(1)))) as char(1) ) as fsrq --0 From his..miHospTemp where bz_code=''and czrq is not null and zy_num is not null and jsdh is not null and keshi is not null and bz_code is not null
试试:select cast(ltrim(rtrim(cast(bz_code as char(21)))) as char(21)) + cast(ltrim(rtrim(cast(czrq as char(11)))) as char(11)) + cast(ltrim(rtrim(cast(zy_num as char(5)))) as char(5) ) + cast(ltrim(rtrim(cast(jsdh as char(21)))) as char(21)) + cast(ltrim(rtrim(cast(keshi as char(41)))) as char(41)) + cast(ltrim(rtrim(cast(price as char(1)))) as char(1) ) + cast(ltrim(rtrim(cast(dept_name as char(1)))) as char(1) ) + cast(ltrim(rtrim(cast(doctor as char(1)))) as char(1) ) + cast(ltrim(rtrim(cast(fsrq as char(1)))) as char(1) ) From his..miHospTemp where bz_code='' and czrq is not null and zy_num is not null and jsdh is not null and keshi is not null and bz_code is not null UNION ALL select cast(class as char(21)) + cast(code as char(11)) + cast(unit as char(5) ) + cast(kong as char(21)) + cast(num as char(41)) + cast(price as char(1) ) + cast(dept_name as char(1) ) + cast(doctor as char(1) ) + cast(fsrq as char(1) ) From his..miHospTemp where bz_code Is Null And czrq Is Null And zy_num Is Null And jsdh Is Null And keshi Is Null
select ltrim(rtrim(cast(bz_code as char(20)))) as bz_code,--为何实际输出长度只有2 ltrim(rtrim(cast(czrq as char(10)))) as czrq,---为何实际输出长度只有11 ltrim(rtrim(cast(zy_num as char(4))))as zy_num,---为何实际输出长度只有2 ltrim(rtrim(cast(jsdh as char(20)))) as jsdh,----实为何际输出长度只有5 ltrim(rtrim(cast(keshi as char(40)))) as keshi,----为何实际输出长度只有8 cast(price as char(10)) as price, cast(dept_name as char(40))as dept_name, cast(doctor as char(10))as doctor, cast(fsrq as char(10))as fsrqFrom his..miHospTemp where bz_code='' and czrq is not null and zy_num is not null and jsdh is not null and keshi is not null and bz_code is not null Union All select ---下面输出的实际长度均正确
cast(class as char(1)), cast(code as char(20)), cast(unit as char(40)), cast(kong as char(1)), cast(num as char(10)), cast(price as char(10)), cast(dept_name as char(40)), cast(doctor as char(10)), cast(fsrq as char(10)) From his..miHospTemp Where bz_code Is Null And czrq Is Null And zy_num Is Null And jsdh Is Null And keshi Is Null------------------------------------------一下是输出的结果------------------------------ 2005-01-12 2 1333 内科 NULL NULL NULL NULL 1 切除术 1 280 内科 111 2005-01-13 1 腺切除术 1 500 内科 222 2005-01-13 ------------------------------------------------------------------------------------------
ltrim(rtrim(cast(bz_code as char(20)))) as bz_code,--为何实际输出长度只有2 ltrim(rtrim(cast(czrq as char(10)))) as czrq,---为何实际输出长度只有11 ltrim(rtrim(cast(zy_num as char(4))))as zy_num,---为何实际输出长度只有2 ltrim(rtrim(cast(jsdh as char(20)))) as jsdh,----实为何际输出长度只有5 ltrim(rtrim(cast(keshi as char(40)))) as keshi,----为何实际输出长度只有8这是因为ltrim函数会首先将其参数隐性转换为varchar,然后截去起始空格。rtrim函数类似,会截去尾随空格。 假设你的bz_code是' abc '(前后各7个空格,字段总长是17),rtrim之后边成 ' abc',然后再ltrim之后变成了'abc',最后字段长度变成了3 可以用这个测试一下: select datalength (ltrim(rtrim(cast(' abc ' as char(20)))))
ltrim(rtrim(cast(bz_code as char(21))))as bz_code
返回的数据长度就不一定是21了,而是实际bz_code的长度
ltrim(rtrim(cast(bz_code as char(21))))as bz_code,--21
ltrim(rtrim(cast(czrq as char(11))))as czrq,--11
ltrim(rtrim(cast(zy_num as char(5))))as zy_num,--5
ltrim(rtrim(cast(jsdh as char(21))))as jsdh,--21
ltrim(rtrim(cast(keshi as char(41))))as keshi,--41
ltrim(rtrim(cast(price as char(1))))as price,---0
ltrim(rtrim(cast(dept_name as char(1))))as dept_name,---0
ltrim(rtrim(cast(doctor as char(1))))as doctor,---0
ltrim(rtrim(cast(fsrq as char(1))))as fsrq---0 From his..miHospTemp
where bz_code=''and czrq is not null and zy_num is not null and jsdh is not null and keshi is not null and bz_code is not null如果我要返回的结果是 21+11+5+21+41=98??,上面大语句改如何写呢^_^
cast(ltrim(rtrim(cast(bz_code as char(21)))) as char(21)) as bz_code,--21
cast(ltrim(rtrim(cast(czrq as char(11)))) as char(11)) as czrq,--11
cast(ltrim(rtrim(cast(zy_num as char(5)))) as char(5) ) as zy_num,--5
cast(ltrim(rtrim(cast(jsdh as char(21)))) as char(21)) as jsdh,--21
cast(ltrim(rtrim(cast(keshi as char(41)))) as char(41)) as keshi,--41
cast(ltrim(rtrim(cast(price as char(1)))) as char(1) ) as price,---0
cast(ltrim(rtrim(cast(dept_name as char(1)))) as char(1) ) as dept_name,---0
cast(ltrim(rtrim(cast(doctor as char(1)))) as char(1) ) as doctor, ---0
cast(ltrim(rtrim(cast(fsrq as char(1)))) as char(1) ) as fsrq --0
From
his..miHospTemp
where
bz_code=''and czrq is not null and zy_num is not null and jsdh is not null and keshi is not null and bz_code is not null
返回结果还是28???
cast(ltrim(rtrim(cast(bz_code as char(21)))) as char(21)) +
cast(ltrim(rtrim(cast(czrq as char(11)))) as char(11)) +
cast(ltrim(rtrim(cast(zy_num as char(5)))) as char(5) ) +
cast(ltrim(rtrim(cast(jsdh as char(21)))) as char(21)) +
cast(ltrim(rtrim(cast(keshi as char(41)))) as char(41)) +
cast(ltrim(rtrim(cast(price as char(1)))) as char(1) ) +
cast(ltrim(rtrim(cast(dept_name as char(1)))) as char(1) ) +
cast(ltrim(rtrim(cast(doctor as char(1)))) as char(1) ) +
cast(ltrim(rtrim(cast(fsrq as char(1)))) as char(1) )
From
his..miHospTemp
where
bz_code=''
and
czrq is not null
and
zy_num is not null
and
jsdh is not null
and
keshi is not null
and
bz_code is not null
UNION ALL
select
cast(class as char(21)) +
cast(code as char(11)) +
cast(unit as char(5) ) +
cast(kong as char(21)) +
cast(num as char(41)) +
cast(price as char(1) ) +
cast(dept_name as char(1) ) +
cast(doctor as char(1) ) +
cast(fsrq as char(1) )
From
his..miHospTemp
where
bz_code Is Null
And
czrq Is Null
And
zy_num Is Null
And
jsdh Is Null
And
keshi Is Null
ltrim(rtrim(cast(bz_code as char(20)))) as bz_code,--为何实际输出长度只有2
ltrim(rtrim(cast(czrq as char(10)))) as czrq,---为何实际输出长度只有11
ltrim(rtrim(cast(zy_num as char(4))))as zy_num,---为何实际输出长度只有2
ltrim(rtrim(cast(jsdh as char(20)))) as jsdh,----实为何际输出长度只有5
ltrim(rtrim(cast(keshi as char(40)))) as keshi,----为何实际输出长度只有8
cast(price as char(10)) as price,
cast(dept_name as char(40))as dept_name,
cast(doctor as char(10))as doctor,
cast(fsrq as char(10))as fsrqFrom his..miHospTemp where bz_code=''
and czrq is not null
and zy_num is not null
and jsdh is not null
and keshi is not null
and bz_code is not null Union All select ---下面输出的实际长度均正确
cast(class as char(1)),
cast(code as char(20)),
cast(unit as char(40)),
cast(kong as char(1)),
cast(num as char(10)),
cast(price as char(10)),
cast(dept_name as char(40)),
cast(doctor as char(10)),
cast(fsrq as char(10)) From his..miHospTemp Where bz_code Is Null
And czrq Is Null
And zy_num Is Null
And jsdh Is Null
And keshi Is Null------------------------------------------一下是输出的结果------------------------------
2005-01-12 2 1333 内科 NULL NULL NULL NULL
1 切除术 1 280 内科 111 2005-01-13
1 腺切除术 1 500 内科 222 2005-01-13
------------------------------------------------------------------------------------------
的方法我觉得是对的。只不过你用bcp导出的时候有没有定义前后缀长度呢?或者选择的什么类型保存?
ltrim(rtrim(cast(czrq as char(10)))) as czrq,---为何实际输出长度只有11
ltrim(rtrim(cast(zy_num as char(4))))as zy_num,---为何实际输出长度只有2
ltrim(rtrim(cast(jsdh as char(20)))) as jsdh,----实为何际输出长度只有5
ltrim(rtrim(cast(keshi as char(40)))) as keshi,----为何实际输出长度只有8这是因为ltrim函数会首先将其参数隐性转换为varchar,然后截去起始空格。rtrim函数类似,会截去尾随空格。
假设你的bz_code是' abc '(前后各7个空格,字段总长是17),rtrim之后边成
' abc',然后再ltrim之后变成了'abc',最后字段长度变成了3
可以用这个测试一下:
select datalength (ltrim(rtrim(cast(' abc ' as char(20)))))
那么为什么我去掉所有的ltrim(rtrim后输出结果又变成这样:^_^select datalength(' 2005-01-15 1 1334 妇科 ')
---------------------------------------------------------------------------148
select datalength('1 根治术 1 500 妇科 2005-01-15')
--------------------------------------------------------------------------------------128
1 腺切除术 1 500 内科 222 2005-01-13
-----------------------------------------------------------------------------------
而第一行的结果根本没有查出来:
2005-01-12 2 1333 内科 NULL NULL NULL NULL
请问为何,查询条件也没变^_^