declare @n int set @n=5 select * from t1 where CHARINDEX('~',a1)>0 and @n between LEFT(t1,1) and RIGHT(t1,1) union all select * from t1 where CHARINDEX('~',a1)=0 and t1=@n
---------------------------------------------------------------------------------- -- Author : htl258(Tony) -- Date : 2010-05-15 11:51:22 -- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2) -- Blog : http://blog.csdn.net/htl258 ------------------------------------------------------------------------------------> 生成测试数据表: [tb] IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb] ([t1] [nvarchar](10)) INSERT INTO [tb] SELECT '1' UNION ALL SELECT '2' UNION ALL SELECT '3' UNION ALL SELECT '4~7' UNION ALL SELECT '8' UNION ALL SELECT '9' -->SQL查询如下: SELECT * FROM [tb] WHERE 5 BETWEEN CAST(left(T1,CHARINDEX('~',T1+'~')-1) AS FLOAT) AND cast(RIGHT(T1,CHARINDEX('~',REVERSE('~'+T1))-1) AS FLOAT) /* t1 ---------- 4~7(1 行受影响) */
declare @n int set @n=5 select * from t1 where CHARINDEX('~',a1)>0 and @n between LEFT(t1,CHARINDEX('~',a1)-1) and STUFF(t1,1,CHARINDEX('~',a1),'') union all select * from t1 where CHARINDEX('~',a1)=0 and t1=@n 修改
--> 生成测试数据表: [tb] IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb] ([t1] [nvarchar](10)) INSERT INTO [tb] SELECT '1' UNION ALL SELECT '2' UNION ALL SELECT '3' UNION ALL SELECT '4~7' UNION ALL SELECT '8' UNION ALL SELECT '9'declare @a int set @a=5select * from [tb] where @a between left([t1],charindex('~',[t1])-1) and right([t1],len([t1])-charindex('~',[t1])) and charindex('~',[t1])>0 /* t1 ---------- 4~7(1 行受影响) */
set @n=5
select *
from t1
where CHARINDEX('~',a1)>0 and @n between LEFT(t1,1) and RIGHT(t1,1)
union all
select *
from t1
where CHARINDEX('~',a1)=0 and t1=@n
-- Author : htl258(Tony)
-- Date : 2010-05-15 11:51:22
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([t1] [nvarchar](10))
INSERT INTO [tb]
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '3' UNION ALL
SELECT '4~7' UNION ALL
SELECT '8' UNION ALL
SELECT '9'
-->SQL查询如下:
SELECT * FROM [tb]
WHERE 5
BETWEEN CAST(left(T1,CHARINDEX('~',T1+'~')-1) AS FLOAT)
AND cast(RIGHT(T1,CHARINDEX('~',REVERSE('~'+T1))-1) AS FLOAT)
/*
t1
----------
4~7(1 行受影响)
*/
set @n=5
select *
from t1
where CHARINDEX('~',a1)>0 and @n between LEFT(t1,CHARINDEX('~',a1)-1) and STUFF(t1,1,CHARINDEX('~',a1),'')
union all
select *
from t1
where CHARINDEX('~',a1)=0 and t1=@n
修改
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([t1] [nvarchar](10))
INSERT INTO [tb]
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '3' UNION ALL
SELECT '4~7' UNION ALL
SELECT '8' UNION ALL
SELECT '9'declare @a int
set @a=5select * from [tb] where
@a between left([t1],charindex('~',[t1])-1) and right([t1],len([t1])-charindex('~',[t1]))
and charindex('~',[t1])>0
/*
t1
----------
4~7(1 行受影响)
*/