有一个表中有一列数据是这样的
Jyouhou
123* 135* 64* CV
123* 89* 64* CV
0.7* 9* 8* CV
123* V* 8* C是固定格式
现在我才分出来成4列去掉前后空格这样
LTRIM(RTRIM(substring(Jyouhou,1,6))) as Jyouhou1 ,
LTRIM(RTRIM(substring(Jyouhou,8,6))) as Jyouhou2,
LTRIM(RTRIM(substring(Jyouhou,15,6))) as Jyouhou3,
LTRIM(RTRIM(substring(Jyouhou,22,6))) as Jyouhou4
后面比较时候用 where Jyouhou1 >'100'时候什么数据都不显示
这是为什么啊 Jyouhou1 〉'1'就显示的
Jyouhou
123* 135* 64* CV
123* 89* 64* CV
0.7* 9* 8* CV
123* V* 8* C是固定格式
现在我才分出来成4列去掉前后空格这样
LTRIM(RTRIM(substring(Jyouhou,1,6))) as Jyouhou1 ,
LTRIM(RTRIM(substring(Jyouhou,8,6))) as Jyouhou2,
LTRIM(RTRIM(substring(Jyouhou,15,6))) as Jyouhou3,
LTRIM(RTRIM(substring(Jyouhou,22,6))) as Jyouhou4
后面比较时候用 where Jyouhou1 >'100'时候什么数据都不显示
这是为什么啊 Jyouhou1 〉'1'就显示的
PARSENAME (replace(Jyouhou,'*','.'),3),
PARSENAME (replace(Jyouhou,'*','.'),2),
PARSENAME (replace(Jyouhou,'*','.'),1)
from ta
where cast(PARSENAME (replace(Jyouhou,'*','.'),4) as int0 > 100
PARSENAME ( Jyouhou, 4) Jyouhou1,
PARSENAME ( Jyouhou, 3) Jyouhou2,
PARSENAME ( Jyouhou, 2) Jyouhou3,
PARSENAME ( Jyouhou, 1) Jyouhou4
from
( select replace(Jyouhou,'*','.') as Jyouhou
from ta) a
where cast(PARSENAME ( Jyouhou,4) as int0 > 100
insert @Jyouhou select '123* 89* 64* CV'
insert @Jyouhou select '0.7* 9* 8* CV'
insert @Jyouhou select '123* V* 8* C'select parsename(replace(col1,'*','.'),4), parsename(replace(col1,'*','.'),3),
parsename(replace(col1,'*','.'),2),parsename(replace(col1,'*','.'),1)
from @Jyouhou
where parsename(replace(col1,'*','.'),4)>100/*
123 135 64 CV
123 89 64 CV
123 V 8 C
*/
go
--> -->
declare @T table([Jyouhou] nvarchar(19))
Insert @T
select N'123* 135* 64* CV' union all
select N'123* 89* 64* CV' union all
select N'0.7* 9* 8* CV' union all
select N'123* V* 8* C'
select
replace(parsename([Jyouhou],4),'~','.') as COl1,
replace(parsename([Jyouhou],3),'~','.') as COl2,
replace(parsename([Jyouhou],2),'~','.') as COl3,
replace(parsename([Jyouhou],1),'~','.') as COl4
from
(Select replace(replace([Jyouhou],'.','~'),'*','*.') as [Jyouhou] from @T)tCOl1 COl2 COl3 COl4
123* 135* 64* CV
123* 89* 64* CV
0.7* 9* 8* CV
123* V* 8* C顯示*號
go
--> -->
declare @T table([Jyouhou] nvarchar(19))
Insert @T
select N'123* 135* 64* CV' union all
select N'123* 89* 64* CV' union all
select N'0.7* 9* 8* CV' union all
select N'123* V* 8* C'
select *
from
(select
replace(parsename([Jyouhou],4),'~','.') as COl1,
replace(parsename([Jyouhou],3),'~','.') as COl2,
replace(parsename([Jyouhou],2),'~','.') as COl3,
replace(parsename([Jyouhou],1),'~','.') as COl4
from
(Select replace(replace([Jyouhou],'.','~'),'*','.') as [Jyouhou] from @T)t)t
where cast(COl1 as decimal(18,2))>100COl1 COl2 COl3 COl4
123 135 64 CV
123 89 64 CV
123 V 8 C
这列有是有字母
如果 where Jyouhou2 〉1 要显示数据 这个怎么办
declare @Jyouhou table (Jyouhou varchar(30))
insert into @Jyouhou
select ' 123* 135* 64 *CV' union all
select ' 123* 89 * 64 *CV' union all
select ' 0.7* 9 * 8 *CV' union all
select ' 123* V * 8 * C'
select * from (
select
ltrim(rtrim(substring(jyouhou,1,6))) as jyouhou1 ,
ltrim(rtrim(substring(jyouhou,8,6))) as jyouhou2 ,
ltrim(rtrim(substring(jyouhou,15,6))) as jyouhou3,
ltrim(rtrim(substring(jyouhou,22,6))) as jyouhou4
from @Jyouhou ) bbwhere jyouhou1>'100'
/*
jyouhou1 jyouhou2 jyouhou3 jyouhou4
------------ ------------ ------------ ------------
123 135 64 *CV
123 89 64 *CV
123 V 8 * C
*/
select * from (
select
ltrim(rtrim(substring(jyouhou,1,6))) as jyouhou1 ,
ltrim(rtrim(substring(jyouhou,8,6))) as jyouhou2 ,
ltrim(rtrim(substring(jyouhou,15,6))) as jyouhou3,
ltrim(rtrim(substring(jyouhou,22,6))) as jyouhou4
from @Jyouhou ) bbwhere jyouhou1>'1'/*
jyouhou1 jyouhou2 jyouhou3 jyouhou4
------------ ------------ ------------ ------------
123 135 64 *CV
123 89 64 *CV
123 V 8 * C
*/
--我这都显示呀