如下:DECLARE @S VARCHAR(2000)
SET @S='HERJFDWVVHISG,HERJJSWVVHIHC,HERIEGWVVHHCB,HERIVRWVVHGIS'输出格式: HERIVRWVVHGIS,HERIEGWVVHHCB,HERJJSWVVHIHC,HERJFDWVVHISG
SET @S='HERJFDWVVHISG,HERJJSWVVHIHC,HERIEGWVVHHCB,HERIVRWVVHGIS'输出格式: HERIVRWVVHGIS,HERIEGWVVHHCB,HERJJSWVVHIHC,HERJFDWVVHISG
SIGHVVWRVIREH,BCHHVVWGEIREH,CHIHVVWSJJREH,GSIHVVWDFJREH(1 row(s) affected)
select REVERSE('HERJFDWVVHISG,HERJJSWVVHIHC,HERIEGWVVHHCB,HERIVRWVVHGIS') -------------------------------------------------------
SIGHVVWRVIREH,BCHHVVWGEIREH,CHIHVVWSJJREH,GSIHVVWDFJREH
-----------------------------------------------------------汗,这哪儿对哦...
select reverse('字符串')
不过楼主可以参考上面的函数,将,分开的部分作为一个整体用Replace取代掉就行了
还是里面是不规律的?
returns varchar(2000)
as
begin
declare @ret varchar(1000)
select @ret='',@instr=','+@instr
while charindex(',',@instr)>0
begin
select @ret =@ret+reverse(left(reverse(@instr),charindex(',',reverse(@instr)))),
@instr=left(@instr,len(@instr)-charindex(',',reverse(@instr)))
end
set @ret=stuff(@ret,1,1,'')
return @ret
end
goDECLARE @S VARCHAR(2000)
SET @S='HERJFDWVVHISG,HERJJSWVVHIHC,HERIEGWVVHHCB,HERIVRWVVHGIS'
select dbo.f_str(@S)/*
-------------------------------------------------------
HERIVRWVVHGIS,HERIEGWVVHHCB,HERJJSWVVHIHC,HERJFDWVVHISG
*/
godrop function f_str
go
SET @S='HERJFDWVVHISG,HERJJSWVVHIHC,HERIEGWVVHHCB,HERIVRWVVHGIS' SELECT
(CASE WHEN PARSENAME(t.COL,1) IS NULL THEN '' else PARSENAME(t.COL,1) END) +
(CASE WHEN PARSENAME(t.COL,2) IS NULL THEN '' else ','+PARSENAME(t.COL,2) END) +
(CASE WHEN PARSENAME(t.COL,3) IS NULL THEN '' else ','+PARSENAME(t.COL,3) END) +
(CASE WHEN PARSENAME(t.COL,4) IS NULL THEN '' else ','+PARSENAME(t.COL,4) END)
FROM
(SELECT REPLACE(@s,',','.') AS COL) t/*
-------------------------------------------------------
HERIVRWVVHGIS,HERIEGWVVHHCB,HERJJSWVVHIHC,HERJFDWVVHISG
*/
SET @A='HERJFDWVVHISG'
SET @B='HERJJSWVVHIHC'
SET @C='HERIEGWVVHHCB'
SET @D='HERIVRWVVHGIS'SET @S='A,B,C,D'
select REPLACE(REPLACE(REPLACE(REPLACE(REVERSE('A,B,C,D'),'A',@A),'B',@B),'C',@C),'D',@D)/*
结果:
-------------------------------------------------------------------------------
HERIVRWVVHGIS,HERIEGWVVHHCB,HERJJSWVVHIHHERIEGWVVHHCB,HERJFHERIVRWVVHGISWVVHISG(1 row(s) affected)
*/
object_piece要返回的对象部分。object_piece 的数据类型为 int 值,可以为下列值。
1 = 对象名称2 = 架构名称3 = 数据库名称4 = 服务器名称
SET @S='HERJFDWVVHISG,HERJJSWVVHIHC,HERIEGWVVHHCB,HERIVRWVVHGIS'+','
select @wz=1,@str=''
while CHARINDEX(',',@s)>0
begin
select @str=substring(@s,@wz,CHARINDEX(',',@s)-1)
set @s=stuff(@s,@wz,CHARINDEX(',',@s),'')
select @new=@str+isnull(','+@new,'')
end
select @new
DECLARE @S VARCHAR(2000)
SET @S='HERJFDWVVHISG,HERJJSWVVHIHC,HERIEGWVVHHCB,HERIVRWVVHGIS' SET @S='HERIVRWVVHGIS,HERIEGWVVHHCB,HERJJSWVVHIHC,HERJFDWVVHISG'
:)又不說明規律,受不了這樣的問題
SET @S='HERJFDWVVHISG,HERJJSWVVHIHC,HERIEGWVVHHCB,HERIVRWVVHGIS'
declare @integers table(i int identity, n bit)
insert @integers(n)
select top 2000 0 from sysobjectsdeclare @split char(1), @s1 varchar(2000)
set @split = ','
set @s1 = ''select i, ltrim(rtrim(substring(@s, i, charindex(@split, @s + @split, i) - i))) as s
into # from @integers
where i <= len(@s + @split) and charindex(@split, @split + @s, i) = i
order by i descupdate # set @s1 = case @s1 when '' then s else @s1 + ',' + s end, s = @s1
select max(s) from #
/*
HERIVRWVVHGIS,HERIEGWVVHHCB,HERJJSWVVHIHC,HERJFDWVVHISG
*/
DECLARE @S VARCHAR(2000)
SET @S='HERJFDWVVHISG,HERJJSWVVHIHC,HERIEGWVVHHCB,HERIVRWVVHGIS'
-- 新增
declare @cStr varchar(200)
declare @nT1 int
declare @nT2 int
set @cStr = ''
set @nT1 = len(@S)%13 + 1
set @nT2 = @nT1select @cStr = @cStr + substring(@S, (@nT2-1)*13+@nT2, 13) + ',', @nT2 = @nT2-1
from master..spt_values
where Name is null and
Number < @nT1
select substring(@cStr, 1, len(@cStr)-1)
-- 输出结果
HERIVRWVVHGIS,HERIEGWVVHHCB,HERJJSWVVHIHC,HERJFDWVVHISG(所影响的行数为 1 行)-- 多一项试试@S='HERJFDWVVHISG,HERJJSWVVHIHC,HERIEGWVVHHCB,HERIVRWVVHGIS,HERIVRWTOMZZU'
-- 输出结果为
HERIVRWTOMZZU,HERIVRWVVHGIS,HERIEGWVVHHCB,HERJJSWVVHIHC,HERJFDWVVHISG(所影响的行数为 1 行)
DECLARE @S VARCHAR(2000)
SET @S='HERJFDWVVHISG,HERJJSWVVHIHC,HERIEGWVVHHCB,HERIVRWVVHGIS'
declare @tt table(id int identity(1,1),col varchar(100))
while @S>''
begin
insert @tt
select LEFT(@S,CHARINDEX(',',@s+',')-1)
set @S=STUFF(@S,1,CHARINDEX(',',@s+','),'')
end
declare @a varchar(8000)
set @a=''
SELECT @a=@a+','+col FROM @TT order by id desc
print stuff(@a,1,1,'')如果有规律的话用 parsename
void reserve_printf(const char* psz)
{
if (!(*psz))
return;
reserve_printf(psz+1);
putchar(*psz);
}
set @str='AB,AC,FED,GH'declare @re_str varchar(100)select @re_str= isnull(@re_str,'')+','+[str]
from
(
SELECT top 100 percent
SUBSTRING(@str,B.number,CHARINDEX(',',@str+',',B.number)-B.number) AS [str],
number
from master.dbo.spt_values AS B
where B.type='p' AND B.number BETWEEN 1 AND LEN(@str)
AND SUBSTRING(','+@str,B.number,1)=','
order by number desc
) Aselect @re_str=stuff(@re_str,1,1,'')
print @re_str