12 个解决方案
#1
declare @s varchar(10)
set @s='1,2,3,4'
select REVERSE(substring(REVERSE(@s),CHARINDEX(',',REVERSE(@s))+1,CHARINDEX(',',REVERSE(@s),CHARINDEX(',',REVERSE(@s))+1)-CHARINDEX(',',REVERSE(@s))-1))
/*
----------
3
(1 行受影响)
*/
#2
这样的话,像“1,2",或者没有子类的会出错啊?
#3
create table tb(id varchar(20))
insert into tb values('1')
insert into tb values('1,2')
insert into tb values('1,2,3')
insert into tb values('1,2,3,4')
go
select id , newid = parsename(replace(id , ',','.'),2) from tb
drop table tb
/*
id newid
-------------------- ----------
1 NULL
1,2 1
1,2,3 2
1,2,3,4 3
(所影响的行数为 4 行)
*/
#4
declare @s varchar(10)
set @s='1,2,3,4'
select s from(
select row_number()over(order by number desc)rn,substring(@s,number,charindex(',',@s+',',number+1)-number)s from master..spt_values
where type='p' and number<=len(@s) and substring(@s,number,1)<>',' and substring(','+@s,number,1)=','
)t where rn=2
/*
s
----------
3
(1 行受影响)
*/
#5
在原字串后加两个逗号.
#6
做个记号
#7
declare @str varchar(10)
set @str='1,2,3,4'
select @str=reverse(','+@str+',')
declare @c varchar(10)
set @c=',4,' --这里换成你的值
select case when charindex(',',@str,charindex(@c,@str)+len(@c))=0 then ''
else substring(@str,
charindex(@c,@str)+len(@c),
(charindex(',',@str,charindex(@c,@str)+len(@c)))-(charindex(@c,@str)+len(@c))
)
end
#8
create table t1
(
col varchar(20)
)
insert into t1 (col) values('1'),('1,2'),('1,2,3'),('1,2,3,4')
select * from t1
;with aaa as
(select REVERSE(col) as col from t1)
select case when CHARINDEX(',',col)>0 then SUBSTRING(col,CHARINDEX(',',col)+1,1) else '' end as col from aaa
#9
create table tb(id varchar(20))
insert into tb values('1')
insert into tb values('1,2')
insert into tb values('1,2,3')
insert into tb values('1,2,3,4')
GO
SELECT
ID=REVERSE(LEFT(ID,CHARINDEX(',',ID+',')-1))
FROM
(SELECT
id=stuff(REVERSE(ID),1,CHARINDEX(',',REVERSE(ID)+','),'')
FROM TB
)t
/*
1
2
3
*/
#10
爱新觉罗.毓华
又向你学习了一个..呵呵.
又向你学习了一个..呵呵.
#11
#12
select id,left(stuff(reverse(id),1,2,''),1) from tb
#1
declare @s varchar(10)
set @s='1,2,3,4'
select REVERSE(substring(REVERSE(@s),CHARINDEX(',',REVERSE(@s))+1,CHARINDEX(',',REVERSE(@s),CHARINDEX(',',REVERSE(@s))+1)-CHARINDEX(',',REVERSE(@s))-1))
/*
----------
3
(1 行受影响)
*/
#2
这样的话,像“1,2",或者没有子类的会出错啊?
#3
create table tb(id varchar(20))
insert into tb values('1')
insert into tb values('1,2')
insert into tb values('1,2,3')
insert into tb values('1,2,3,4')
go
select id , newid = parsename(replace(id , ',','.'),2) from tb
drop table tb
/*
id newid
-------------------- ----------
1 NULL
1,2 1
1,2,3 2
1,2,3,4 3
(所影响的行数为 4 行)
*/
#4
declare @s varchar(10)
set @s='1,2,3,4'
select s from(
select row_number()over(order by number desc)rn,substring(@s,number,charindex(',',@s+',',number+1)-number)s from master..spt_values
where type='p' and number<=len(@s) and substring(@s,number,1)<>',' and substring(','+@s,number,1)=','
)t where rn=2
/*
s
----------
3
(1 行受影响)
*/
#5
在原字串后加两个逗号.
#6
做个记号
#7
declare @str varchar(10)
set @str='1,2,3,4'
select @str=reverse(','+@str+',')
declare @c varchar(10)
set @c=',4,' --这里换成你的值
select case when charindex(',',@str,charindex(@c,@str)+len(@c))=0 then ''
else substring(@str,
charindex(@c,@str)+len(@c),
(charindex(',',@str,charindex(@c,@str)+len(@c)))-(charindex(@c,@str)+len(@c))
)
end
#8
create table t1
(
col varchar(20)
)
insert into t1 (col) values('1'),('1,2'),('1,2,3'),('1,2,3,4')
select * from t1
;with aaa as
(select REVERSE(col) as col from t1)
select case when CHARINDEX(',',col)>0 then SUBSTRING(col,CHARINDEX(',',col)+1,1) else '' end as col from aaa
#9
create table tb(id varchar(20))
insert into tb values('1')
insert into tb values('1,2')
insert into tb values('1,2,3')
insert into tb values('1,2,3,4')
GO
SELECT
ID=REVERSE(LEFT(ID,CHARINDEX(',',ID+',')-1))
FROM
(SELECT
id=stuff(REVERSE(ID),1,CHARINDEX(',',REVERSE(ID)+','),'')
FROM TB
)t
/*
1
2
3
*/
#10
爱新觉罗.毓华
又向你学习了一个..呵呵.
又向你学习了一个..呵呵.
#11
#12
select id,left(stuff(reverse(id),1,2,''),1) from tb