补充:表结构为 ID NUM 1 001-023 2 024-089 3 090-100 现在用条件 NUM='025' 去查询得到 ID=2 SQL怎么写?
用条件 NUM='025' 去查询得到 ID=2 ????不知道怎么查....
select * from tb where charindex('-025-','-'+COL+'-')>0
declare @num varchar(10) set @num='025' select id from tb where @num between parsename(replace(num,'-','.'),2) and parsename(replace(num,'-','.'),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 行受影响) */
---------------------------------------------------------------- -- 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 ( 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 行受影响) */
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))
ID NUM
1 001-023
2 024-089
3 090-100
现在用条件 NUM='025' 去查询得到 ID=2
SQL怎么写?
????不知道怎么查....
declare @num varchar(10)
set @num='025'
select id from tb where @num between parsename(replace(num,'-','.'),2) and parsename(replace(num,'-','.'),1)
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 行受影响)
*/
-- 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 行受影响)
*/
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 行受影响)
*/
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))
fredrickhu 方法最好又简单