22,210
社区成员
发帖
与我相关
我的任务
分享
declare @t table (
id int not null,
Num varchar(8) not null
)
insert into @t
select 1, '001-023' union all
select 2, '024-089' union all
select 3, '090-100'
declare @n varchar(4)
set @n = '025'
select * from @t
where @n>=left(num,charindex('-',num)-1) and @n<=right(num,len(num)-charindex('-',num))
--> 测试数据:[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([ID] INT,[NUM] VARCHAR(7))
INSERT [tb]
SELECT 1,'001-023' UNION ALL
SELECT 2,'024-089' UNION ALL
SELECT 3,'090-100'
--------------开始查询--------------------------
DECLARE @i VARCHAR(10)
SET @i='025'
SELECT * FROM
(
SELECT * FROM [tb] WHERE CHARINDEX('-',[NUM])>0--先查询出来包含有-的,如果数据都包含有这个,则不需要这一步
)
t WHERE @i BETWEEN LEFT([NUM],CHARINDEX('-',[NUM])-1) AND RIGHT([NUM],CHARINDEX('-',[NUM])-1)
----------------结果----------------------------
/*
ID NUM
----------- -------
2 024-089
(1 行受影响)
*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2012-07-10 14:48:37
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[NUM] varchar(7))
insert [tb]
select 1,'001-023' union all
select 2,'024-089' union all
select 3,'090-100'
--------------开始查询--------------------------
declare @num varchar(10)
set @num='025'
select id from tb where @num between parsename(replace(num,'-','.'),2) and parsename(replace(num,'-','.'),1)
----------------结果----------------------------
/* id
-----------
2
(1 行受影响)
*/
--> 测试数据:[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([ID] INT,[NUM] VARCHAR(7))
INSERT [tb]
SELECT 1,'001-023' UNION ALL
SELECT 2,'024-089' UNION ALL
SELECT 3,'090-100'
--------------开始查询--------------------------
DECLARE @i VARCHAR(10)
SET @i='025'
SELECT * FROM [tb] WHERE @i BETWEEN LEFT([NUM],CHARINDEX('-',[NUM])-1) AND RIGHT([NUM],CHARINDEX('-',[NUM])-1)
----------------结果----------------------------
/*
ID NUM
----------- -------
2 024-089
(1 行受影响)
*/
declare @num varchar(10)
set @num='025'
select id from tb where @num between parsename(replace(num,'-','.'),2) and parsename(replace(num,'-','.'),1)
select * from tb where charindex('-025-','-'+COL+'-')>0