数据表格式与数据如下price varchar(10)price字段中的含有如下格式的数据:10080-10050-1002000这样查询会发生错误:SELECT price FROM [tbName] WHERE Ceiling(price)<100
改成这样80-100的数据会忽略掉:SELECT price FROM [tbName] WHERE (IsNumeric(price)=1 AND Ceiling(price)<100如何在T-SQL中将80-100分割,SQL2000的T-SQL语句中不包含 split 函数,所以求助各位高手给个DEMO注:只能在T-SQL查询的WHERE条件中处理,不是在程序中。
改成这样80-100的数据会忽略掉:SELECT price FROM [tbName] WHERE (IsNumeric(price)=1 AND Ceiling(price)<100如何在T-SQL中将80-100分割,SQL2000的T-SQL语句中不包含 split 函数,所以求助各位高手给个DEMO注:只能在T-SQL查询的WHERE条件中处理,不是在程序中。
SELECT price FROM [tbName]
WHERE (IsNumeric(price)=1 AND Ceiling(price)<100
or price like '%-100'
if object_id('tempdb.dbo.#a') is not null drop table #a
go
create table #a(price varchar(10))
insert into #a
select '100' union all
select '80-100' union all
select '50-100'select price,isnull(parsename(REPLACE(price,'-','.'),2),price) as min,parsename(REPLACE(price,'-','.'),1) as max
from #a
/*
price min max
100 100 100
80-100 80 100
50-100 50 100
*/
谢谢,结贴。