--> 测试数据:[test] if object_id('[test]') is not null drop table [test] create table [test]([value] varchar(15)) insert [test] select '67|085B|RB|SDE' union all select 'ER322|K5|34|123';with t as( select SUBSTRING([value], CHARINDEX('|',[value])+1,LEN([value])-CHARINDEX('|',[value])-1) as value from test ) select SUBSTRING([value],CHARINDEX('|',[value])+1, len(value)-CHARINDEX('|',value,CHARINDEX('|',[value])+1)) as value from t/* value RB 34 */
if object_id('[test]') is not null drop table [test] create table [test]([value] varchar(50)) insert [test] select '67|085B|RB|SDE23' union all select 'ER322|K5|34|123' select 'H2|K222|F4880|H234323';with t as( select SUBSTRING([value], CHARINDEX('|',[value])+1,LEN([value])-CHARINDEX('|',[value])-1) as value from test ) select SUBSTRING([value],CHARINDEX('|',[value])+1, len(value)-CHARINDEX('|',value,CHARINDEX('|',[value])+1)) as value from t改成这样好象不对! 请再帮忙看看,谢谢!
if object_id('[test]') is not null drop table [test] create table [test]([value] varchar(50)) insert [test] select '67|085B|RB|SDE23' union all select 'ER322|K5|34|123' union all select 'H2|K222|F4880|H234323';with t as( select SUBSTRING([value], CHARINDEX('|',[value])+1,LEN([value])-CHARINDEX('|',[value])-1) as value from test ) select SUBSTRING([value],CHARINDEX('|',[value])+1, CHARINDEX('|',value,CHARINDEX('|',[value])+1)-CHARINDEX('|',[value])-1) as value from t/* value RB 34 F4880 */ --我搞错了,应该是这样才对,不好意思
不知为何,因我的记录特别多,前面取出了很多,到后面却出现了如下提示: 消息 537,级别 16,状态 3,第 1 行 Invalid length parameter passed to the LEFT or SUBSTRING function.
67|085B|RB|SDE 这个一共多少|?如果在4个之内 可以select parsename(replace(col,'|','.'),2) from tb
if object_id('[test]') is not null drop table [test] create table [test]([value] varchar(50)) insert [test] select '67|085B|RB' union all select 'ER322|K5' union all select 'H2|K222|FG44' --只有两个的情况下 select right([value], CHARINDEX('|',REVERSE([value]))-1) as value from test/* value RB K5 FG44 */
declare @a1 varchar(30),@a2 varchar(1) set @a2='|' set @a1='1|2|' select case when len(@a1)-len(replace(@a1,'|',''))>2 then substring(@a1,charindex(@a2,@a1,charindex(@a2,@a1,1)+1)+1,charindex(@a2,@a1,charindex(@a2,@a1,charindex(@a2,@a1,1)+1)+1)-charindex(@a2,@a1,charindex(@a2,@a1,1)+1)-1) else '' end判断一下 如果里面有2个以上的|就截取字符串,否则就为''
if object_id('[test]') is not null drop table [test] create table [test]([value] varchar(50)) insert [test] select '67|085B|RB|SDE23' union all select 'ER322|K5|34|123' union all select 'H2|K222|F4880|H234323' union all select 'DF|45JH|FGF'--只含有两个,不需判断;with t as( select SUBSTRING([value]+'|', CHARINDEX('|',[value]+'|')+1,LEN([value]+'|')-CHARINDEX('|',[value]+'|')-1) as value from test ) select SUBSTRING([value]+'|',CHARINDEX('|',[value]+'|')+1, CHARINDEX('|',value+'|',CHARINDEX('|',[value]+'|')+1)-CHARINDEX('|',[value]+'|')-1) as value from t/* value RB 34 F4880 FGF */ --这样就好了
--> 测试数据:[test]
if object_id('[test]') is not null
drop table [test]
create table [test]([value] varchar(15))
insert [test]
select '67|085B|RB|SDE' union all
select 'ER322|K5|34|123';with t
as(
select SUBSTRING([value],
CHARINDEX('|',[value])+1,LEN([value])-CHARINDEX('|',[value])-1) as value
from test
)
select SUBSTRING([value],CHARINDEX('|',[value])+1,
len(value)-CHARINDEX('|',value,CHARINDEX('|',[value])+1)) as value
from t/*
value
RB
34
*/
drop table [test]
create table [test]([value] varchar(50))
insert [test]
select '67|085B|RB|SDE23' union all
select 'ER322|K5|34|123'
select 'H2|K222|F4880|H234323';with t
as(
select SUBSTRING([value],
CHARINDEX('|',[value])+1,LEN([value])-CHARINDEX('|',[value])-1) as value
from test
)
select SUBSTRING([value],CHARINDEX('|',[value])+1,
len(value)-CHARINDEX('|',value,CHARINDEX('|',[value])+1)) as value
from t改成这样好象不对! 请再帮忙看看,谢谢!
if object_id('[test]') is not null
drop table [test]
create table [test]([value] varchar(50))
insert [test]
select '67|085B|RB|SDE23' union all
select 'ER322|K5|34|123' union all
select 'H2|K222|F4880|H234323';with t
as(
select SUBSTRING([value],
CHARINDEX('|',[value])+1,LEN([value])-CHARINDEX('|',[value])-1) as value
from test
)
select SUBSTRING([value],CHARINDEX('|',[value])+1,
CHARINDEX('|',value,CHARINDEX('|',[value])+1)-CHARINDEX('|',[value])-1) as value
from t/*
value
RB
34
F4880
*/
--我搞错了,应该是这样才对,不好意思
消息 537,级别 16,状态 3,第 1 行
Invalid length parameter passed to the LEFT or SUBSTRING function.
这个一共多少|?如果在4个之内 可以select parsename(replace(col,'|','.'),2) from tb
if object_id('[test]') is not null
drop table [test]
create table [test]([value] varchar(50))
insert [test]
select '67|085B|RB' union all
select 'ER322|K5' union all
select 'H2|K222|FG44'
--只有两个的情况下
select right([value],
CHARINDEX('|',REVERSE([value]))-1) as value
from test/*
value
RB
K5
FG44
*/
set @a2='|'
set @a1='1|2|'
select case when len(@a1)-len(replace(@a1,'|',''))>2 then substring(@a1,charindex(@a2,@a1,charindex(@a2,@a1,1)+1)+1,charindex(@a2,@a1,charindex(@a2,@a1,charindex(@a2,@a1,1)+1)+1)-charindex(@a2,@a1,charindex(@a2,@a1,1)+1)-1)
else '' end判断一下 如果里面有2个以上的|就截取字符串,否则就为''
if object_id('[test]') is not null
drop table [test]
create table [test]([value] varchar(50))
insert [test]
select '67|085B|RB|SDE23' union all
select 'ER322|K5|34|123' union all
select 'H2|K222|F4880|H234323' union all
select 'DF|45JH|FGF'--只含有两个,不需判断;with t
as(
select SUBSTRING([value]+'|',
CHARINDEX('|',[value]+'|')+1,LEN([value]+'|')-CHARINDEX('|',[value]+'|')-1) as value
from test
)
select SUBSTRING([value]+'|',CHARINDEX('|',[value]+'|')+1,
CHARINDEX('|',value+'|',CHARINDEX('|',[value]+'|')+1)-CHARINDEX('|',[value]+'|')-1) as value
from t/*
value
RB
34
F4880
FGF
*/
--这样就好了