select
substring([模具尺寸],charindex('A',[模具尺寸])+1,(charindex('B',[模具尺寸])-charindex('A',[模具尺寸]))-1)
from cost1
where
substring([模具尺寸],charindex('A',[模具尺寸])+1,(charindex('B',[模具尺寸])-charindex('A',[模具尺寸]))-1) between 70 and 90以下的字段值:(加条件)
DCI4560A90B100C120
DCI5070A80B120C120
CI2340A70B90C80
CI1520A50B60C60
CI1520A50B60C60
DCI4050A100B110C120
DCI4050A70B90C120
CI3550A60B90C100
DCI3035A50B100C90
CI3035A70B80C100
CI3030A60B70C90
DCI2325A50B70C80
CI1820A50B60C60
CI1520A50B60C60
CI1820A50B60C60
DCI2530A40B70C80
CI1520A40B60C60
CI3555A70B90C100
DCI2540A70B80C80
---------------
以下是查询值:
90
80
70
70
70
70
70
它显示7条.正确的应是18条
substring([模具尺寸],charindex('A',[模具尺寸])+1,(charindex('B',[模具尺寸])-charindex('A',[模具尺寸]))-1)
from cost1
where
substring([模具尺寸],charindex('A',[模具尺寸])+1,(charindex('B',[模具尺寸])-charindex('A',[模具尺寸]))-1) between 70 and 90以下的字段值:(加条件)
DCI4560A90B100C120
DCI5070A80B120C120
CI2340A70B90C80
CI1520A50B60C60
CI1520A50B60C60
DCI4050A100B110C120
DCI4050A70B90C120
CI3550A60B90C100
DCI3035A50B100C90
CI3035A70B80C100
CI3030A60B70C90
DCI2325A50B70C80
CI1820A50B60C60
CI1520A50B60C60
CI1820A50B60C60
DCI2530A40B70C80
CI1520A40B60C60
CI3555A70B90C100
DCI2540A70B80C80
---------------
以下是查询值:
90
80
70
70
70
70
70
它显示7条.正确的应是18条
substring([模具尺寸],charindex('A',[模具尺寸])+1,(charindex('B',[模具尺寸])-charindex('A',[模具尺寸]))-1)
from cost1
where
substring([模具尺寸],charindex('A',[模具尺寸])+1,(charindex('B',[模具尺寸])-charindex('A',[模具尺寸]))-1) >= 70 and substring([模具尺寸],charindex('A',[模具尺寸])+1,(charindex('B',[模具尺寸])-charindex('A',[模具尺寸]))-1) <= 90
substring([模具尺寸],charindex('A',[模具尺寸])+1,(charindex('B',[模具尺寸])-charindex('A',[模具尺寸]))-1)
from cost1
where
substring([模具尺寸],charindex('A',[模具尺寸])+1,(charindex('B',[模具尺寸])-charindex('A',[模具尺寸]))-1) >= '70' and substring([模具尺寸],charindex('A',[模具尺寸])+1,(charindex('B',[模具尺寸])-charindex('A',[模具尺寸]))-1) <= '90'
substring([模具尺寸],charindex('A',[模具尺寸])+1,(charindex('B',[模具尺寸])-charindex('A',[模具尺寸]))-1)
from cost1
where
substring([模具尺寸],charindex('A',[模具尺寸])+1,(charindex('B',[模具尺寸])-charindex('A',[模具尺寸]))-1) between 70 and 90---------------- lz自己这里加了条件限制当然不可能输出全部结果咯
drop table tb
gocreate table tb
(
模具尺寸 varchar(50)
)insert into tb(模具尺寸) values('DCI4560A90B100C120')
insert into tb(模具尺寸) values('DCI5070A80B120C120')
insert into tb(模具尺寸) values('CI2340A70B90C80')
insert into tb(模具尺寸) values('CI1520A50B60C60')
insert into tb(模具尺寸) values('CI1520A50B60C60')
insert into tb(模具尺寸) values('DCI4050A100B110C120')
insert into tb(模具尺寸) values('DCI4050A70B90C120')
insert into tb(模具尺寸) values('CI3550A60B90C100')
insert into tb(模具尺寸) values('DCI3035A50B100C90')
insert into tb(模具尺寸) values('CI3035A70B80C100')
insert into tb(模具尺寸) values('CI3030A60B70C90')
insert into tb(模具尺寸) values('DCI2325A50B70C80')
insert into tb(模具尺寸) values('CI1820A50B60C60')
insert into tb(模具尺寸) values('CI1520A50B60C60')
insert into tb(模具尺寸) values('CI1820A50B60C60')
insert into tb(模具尺寸) values('DCI2530A40B70C80')
insert into tb(模具尺寸) values('CI1520A40B60C60')
insert into tb(模具尺寸) values('CI3555A70B90C100')
insert into tb(模具尺寸) values('DCI2540A70B80C80')select 模具尺寸 =
substring([模具尺寸],charindex('A',[模具尺寸])+1,(charindex('B',[模具尺寸])-charindex('A',[模具尺寸]))-1)
from tb
where
substring([模具尺寸],charindex('A',[模具尺寸])+1,(charindex('B',[模具尺寸])-charindex('A',[模具尺寸]))-1) >= '70' and substring([模具尺寸],charindex('A',[模具尺寸])+1,(charindex('B',[模具尺寸])-charindex('A',[模具尺寸]))-1) <= '90'drop table tb--结果,确实只有7条
模具尺寸
--------
90
80
70
70
70
70
70(所影响的行数为 7 行)
因为有条件 between 70 and 90