有表A
字段 a b c
值 K1 1 X
L1/L3 3 X
22 2 Y求一个查询
外部传递一个变量I,当字段b值为3时,如果I为1,字段a返回“/”前的字符,如果I为2,字段a返回“/”后的字符,否则返回字段a
传递变量I=1 返回
K1 1 X
L1 3 X
22 2 Y
传递变量I=2 返回
K1 1 X
L3 3 X
22 2 Y
谢谢
字段 a b c
值 K1 1 X
L1/L3 3 X
22 2 Y求一个查询
外部传递一个变量I,当字段b值为3时,如果I为1,字段a返回“/”前的字符,如果I为2,字段a返回“/”后的字符,否则返回字段a
传递变量I=1 返回
K1 1 X
L1 3 X
22 2 Y
传递变量I=2 返回
K1 1 X
L3 3 X
22 2 Y
谢谢
insert into tb values('K1' , 1 , 'X')
insert into tb values('L1/L3' , 3 , 'X')
insert into tb values('22' , 2 , 'Y')
go
declare @i as intset @i = 1
select * from tb where b <> 3
union all
select case when @i = 1 then left(a , charindex('/',a) -1) when @i = 2 then substring(a , charindex('/',a) + 1 , len(a)) else a end , b, c from tb where b = 3
/*
a b c
---------- ---------- ----------
K1 1 X
22 2 Y
L1 3 X(所影响的行数为 3 行)
*/set @i = 2
select * from tb where b <> 3
union all
select case when @i = 1 then left(a , charindex('/',a) -1) when @i = 2 then substring(a , charindex('/',a) + 1 , len(a)) else a end , b, c from tb where b = 3
/*
a b c
---------- ---------- ----------
K1 1 X
22 2 Y
L3 3 X(所影响的行数为 3 行)
*/drop table tb
insert into tb values('K1' , 1 , 'X')
insert into tb values('L1/L3' , 3 , 'X')
insert into tb values('22' , 2 , 'Y')
go
declare @i as intset @i = 1
select case when @i = 1 and b = 3 then left(a , charindex('/',a) -1) when @i = 2 and b = 3 then substring(a , charindex('/',a) + 1 , len(a)) else a end a, b, c from tb
/*
a b c
---------- ---------- ----------
K1 1 X
L1 3 X
22 2 Y(所影响的行数为 3 行)
*/set @i = 2
select case when @i = 1 and b = 3 then left(a , charindex('/',a) -1) when @i = 2 and b = 3 then substring(a , charindex('/',a) + 1 , len(a)) else a end a, b, c from tb
/*
a b c
---------- ---------- ----------
K1 1 X
L3 3 X
22 2 Y(所影响的行数为 3 行)
*/drop table tb
insert into tb values('K1' , 1 , 'X')
insert into tb values('L1/L3' , 3 , 'X')
insert into tb values('22' , 2 , 'Y')
go
declare @i as intset @i = 1
select case when @i = 1 and b = 3 then left(a , charindex('/',a) -1) when @i = 2 and b = 3 then substring(a , charindex('/',a) + 1 , len(a)) else a end a, b, c from tb
/*
a b c
---------- ---------- ----------
K1 1 X
L1 3 X
22 2 Y(所影响的行数为 3 行)
*/set @i = 2
select case when @i = 1 and b = 3 then left(a , charindex('/',a) -1) when @i = 2 and b = 3 then substring(a , charindex('/',a) + 1 , len(a)) else a end a, b, c from tb
/*
a b c
---------- ---------- ----------
K1 1 X
L3 3 X
22 2 Y(所影响的行数为 3 行)
*/drop table tb
insert into tb values('K1' , 1 , 'X')
insert into tb values('L1/L3' , 3 , 'X')
insert into tb values('22' , 2 , 'Y')
go
declare @i as intset @i = 1
select case when @i = 1 and b = 3 then left(a , charindex('/',a) -1) when @i = 2 and b = 3 then substring(a , charindex('/',a) + 1 , len(a)) else a end a, b, c from tb
/*
a b c
---------- ---------- ----------
K1 1 X
L1 3 X
22 2 Y(所影响的行数为 3 行)
*/set @i = 2
select case when @i = 1 and b = 3 then left(a , charindex('/',a) -1) when @i = 2 and b = 3 then substring(a , charindex('/',a) + 1 , len(a)) else a end a, b, c from tb
/*
a b c
---------- ---------- ----------
K1 1 X
L3 3 X
22 2 Y(所影响的行数为 3 行)
*/drop table tb
insert into tb values('K1' , 1 , 'X')
insert into tb values('L1/L3' , 3 , 'X')
insert into tb values('22' , 2 , 'Y')
go
declare @i as intset @i = 1
select case when @i = 1 and b = 3 then left(a , charindex('/',a) -1) when @i = 2 and b = 3 then substring(a , charindex('/',a) + 1 , len(a)) else a end a, b, c from tb
/*
a b c
---------- ---------- ----------
K1 1 X
L1 3 X
22 2 Y(所影响的行数为 3 行)
*/set @i = 2
select case when @i = 1 and b = 3 then left(a , charindex('/',a) -1) when @i = 2 and b = 3 then substring(a , charindex('/',a) + 1 , len(a)) else a end a, b, c from tb
/*
a b c
---------- ---------- ----------
K1 1 X
L3 3 X
22 2 Y(所影响的行数为 3 行)
*/drop table tb
(
a nvarchar(20),
b int,
c nvarchar(20)
)
insert into #F
select 'K1' , 1 ,'X' union all
select 'L1/L3', 3 , 'X' union all
select '22' , 2 , 'Y'
declare @I int
set @I=2
select a,b,c from #F where CHARINDEX('/',a)<=0
union all
select (case @I when 1 then SUBSTRING(a,1,CHARINDEX('/',a)-1) when 2 then
SUBSTRING(a,CHARINDEX('/',a)+1,LEN(a)-CHARINDEX('/',a)) end) 'a',b,c
from #F
where CHARINDEX('/',a)>0 and b=3
drop table #F
insert into tb values('K1' , 1 , 'X')
insert into tb values('L1/L3' , 3 , 'X')
insert into tb values('22' , 2 , 'Y') create table #(a varchar(10),b varchar(10),c varchar(10),d int)
go
insert into #(a,b,c,d)
select a,b,c,0 from tb where charindex('/',a)=0
union
select substring(a ,1, charindex('/',a)-1),b,c,1 from tb where charindex('/',a)<>0
union
select substring(a , charindex('/',a) + 1 , len(a)) ,b,c,2 from tb where charindex('/',a)<>0
go--I=1
select a,b,c from # where d<>2
--I=2
select a,b,c from # where d<>1drop table #
gojie yong le dian da wu gui de dai ma
CREATE TABLE [A] (
[a] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[b] [smallint] NULL ,
[c] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
-----------------------------
declare @flag char(10)
If @flag = 'I'
Begin
Select Left(Rtrim(a),Len(Rtrim(a)) - Charindex('/',Rtrim(a),1)) From A where b = 3
Union all
Select a From a where b<>3
End
Else
Select Right(Rtrim(a),Len(Rtrim(a)) - Charindex('/',Rtrim(a),1)) From A where b = 3
Union all
Select a From a where b<>3
CREATE TABLE [A] (
[a] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[b] [smallint] NULL ,
[c] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
----------------------------- declare @flag char(10)
Set @Flag = '9'
If @flag = '1'
Begin
Select Left(Rtrim(a),Len(Rtrim(a)) - Charindex('/',Rtrim(a),1)) From A where b = 3
Union all
Select a From a where b<>3
End
Else
If @flag = '2'
Begin
Select Right(Rtrim(a),Len(Rtrim(a)) - Charindex('/',Rtrim(a),1)) From A where b = 3
Union all
Select a From a where b<>3
End
Else
Select * From A