简单字符串处理

Question

字符串=8C8E264D2DFF053595EB70733D6502B7B10BE42D09870F591E41A3B30903
这个字符串的值是可变的，能否做为一个参数传递。
想要转换成
8C8E26,4D2DFF,053595,EB7073,3D6502,B7B10B,E42D09,870F59,1E41A3,B30903
每六位需要加一个逗号。请大家帮忙转换。

Answer 1

ALTER FUNCTION INSERTSTR(@S VARCHAR(8000))
RETURNS VARCHAR(8000)
AS 
  BEGIN
DECLARE @RESULT VARCHAR(8000)
SET @RESULT=''
        WHILE LEN(@S)>0 
        BEGIN
             SET @RESULT=@RESULT+CASE WHEN LEN(@S)>6 THEN SUBSTRING(@S,1,6)+',' ELSE SUBSTRING(@S,1,LEN(@S))END
     SET @S=CASE WHEN LEN(@S)>6 THEN STUFF(@S,1,6,'') ELSE STUFF(@S,1,LEN(@S),'') END
        END
  RETURN @RESULT
  ENDDECLARE @S VARCHAR(8000)
SET @S='8C8E264D2DFF053595EB70733D6502B7B10BE42D09870F591E41A3B30903'
SELECT DBO.INSERTSTR(@S)
                                                                                                                                                                                                                                                                 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
8C8E26,4D2DFF,053595,EB7073,3D6502,B7B10B,E42D09,870F59,1E41A3,B30903（所影响的行数为 1 行）

Answer 2

declare @O varchar(1000),@N varchar(1000)
select @N='',@O='8C8E264D2DFF053595EB70733D6502B7B10BE42D09870F591E41A3B30903'
while len(@O)>6 
      begin
        set @N=@N+left(@O,6)+','
        set @O=stuff(@O,1,6,'')
      end 
select @n+@o
/*
8C8E26,4D2DFF,053595,EB7073,3D6502,B7B10B,E42D09,870F59,1E41A3,B30903(1 個資料列受到影響)
*/
      

Answer 3

DECLARE @a table(id INT IDENTITY,b INT )
INSERT @a SELECT TOP 100 0 FROM syscolumns sDECLARE @b varchar(100)
DECLARE @sql varchar(100)
SET @B='8C8E264D2DFF053595EB70733D6502B7B10BE42D09870F591E41A3B30903'SELECT @sql=isnull(@sql+',','')+substring(@b,id,6)
FROM @a 
WHERE id %6=1 AND substring(@b,id,6)<>''SELECT @sql--result
/*-----------------------------------------------------------------------------
8C8E26,4D2DFF,053595,EB7073,3D6502,B7B10B,E42D09,870F59,1E41A3,B30903（所影响的行数为 1 行）
*/

Answer 4

declare @s varchar(1000),@r varchar(1000)
set @s='8C8E264D2DFF053595EB70733D6502B7B10BE42D09870F591E41A3B309031'
while(len(@s)>=6)
begin
  set @r=isnull(@r+',','')+substring(@s,1,6)
  set @s=right(@s,len(@s)-6)
end
print @r+case when len(@s)>0 then ','+@s else '' end/**8C8E26,4D2DFF,053595,EB7073,3D6502,B7B10B,E42D09,870F59,1E41A3,B30903,1**/

Answer 5

declare @str nvarchar(100), @n int , @re nvarchar(150)
set @n=1
set @str='abcdefghijklmnopqrstuvwxyz'
while  charindex( ','  , reverse(','+@str) )>7
begin
   select @str=stuff(@str,7*@n,0,',')
   set @n=@n+1
endprint @str

Answer 6

declare @str varchar(100) 
set @str = '8C8E264D2DFF053595EB70733D6502B7B10BE42D09870F591E41A3B30903'declare @data varchar(100)select @data = isnull(@data+',','')+substring(value,(number)*6+1,6)
from master..spt_values a
inner join(select @str as value) b on a.number < len(value)/6 and type=  'p'
select @data---------------------------------------------------------------------------------------------------- 
8C8E26,4D2DFF,053595,EB7073,3D6502,B7B10B,E42D09,870F59,1E41A3,B30903（所影响的行数为 1 行）

Answer 7

declare @str varchar(8000),@return varchar(8000)select @str = '8C8E264D2DFF053595EB70733D6502B7B10BE42D09870F591E41A3B30903'
while len(@str) > 0 
begin
select @return = isnull(@return+',','')+substring(@str,1,6)
select @str = stuff(@str,1,6,'')
endselect @return