如何通过分裂获得记录 [英] How Can I Get Record By Spliting

问题描述

你好



我有一个字符串，我希望不按特定方式拆分



例如

Hello

I have a string and i want no split in specific manner

For Example

declare @s varchar(500) = 'Paul Cameron Adi Hardy'

来自这个字符串我想要一个结果如

from this string i want a result like

Paul 
Paul Cameron 
Paul Cameron Adi 
Paul Cameron Adi Hardy
Cameron
Cameron Adi
Cameron Adi Hardy
Adi
Adi Hardy
Hardy

i希望按顺序按顺序连接每个组合。

我怎样才能完成。请建议我。

谢谢

i want a work concate in sequence of each combination in sequence.
how can i accomplish . please suggest me.
Thanks

推荐答案

试试这个：这个样本适用于你的第一个设置你可以将while循环逻辑更改为所有下一个单词。

Hi,
Try this : This sample will work for your first set you can change the while loop logic to all next words .

    declare @Str varchar(8000) = 'Paul Cameron Adi Hardy',
    @delimiter as varchar(10)=' ',
    @OldStr varchar(8000) =''
     declare @ival int     
    declare @splits varchar(8000)     
    
    IF OBJECT_ID('tempdb..#temptable') IS NOT NULL                                                                          
    DROP TABLE #temptable  
    CREATE TABLE #temptable  
(  
 
   Items     VARCHAR(500)  
)  
    select @ival = 1     
        if len(@Str)<1 or @Str is null  return     
   
    while @ival!= 0     
    begin    
  
        set @ival = charindex(@Delimiter,@Str)     
        if @ival!=0     
            set @splits = left(@Str,@ival - 1)     
        else     
            set @splits = @Str     
        set @OldStr=@OldStr + ' ' + @splits
     
        if(len(@splits)>0)
            insert into #temptable(Items) values(@OldStr)     
           -- select @splits into #temptable

        set @Str = right(@Str,len(@Str) - @ival)     
        if len(@Str) = 0 break     
    end 
select * from #temptable

运行此函数：

Run this Functions:

create FUNCTION [dbo].[Split]
(
	@List nvarchar(2000),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
		
	Id int identity(1,1),
	Value nvarchar(100)
) 
AS  
BEGIN
	While (Charindex(@SplitOn,@List)>0)
	Begin 
		Insert Into @RtnValue (value)
		Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) 
			Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
	End 
	Insert Into @RtnValue (Value)
	Select Value = ltrim(rtrim(@List))
	Return
END

和

and

CREATE FUNCTION [dbo].[Split1]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE 
AS
RETURN 
(
    WITH Split(stpos,endpos) 
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)

您期望的结果：

You expected Result Here :

drop table #table
 create  table #table (id int,data nvarchar(100));

 declare @start int =1,@end int=10,@str1 nvarchar(max),@reset int=1,@intial int=1;
 declare @Str varchar(8000) = 'Paul Cameron Adi Hardy';
 insert into #table(id,data ) select * from dbo.Split1(@str,' ')
 while(@start<=@end)
 begin

 SELECT @str1= STUFF((SELECT ',' + cast(id as nvarchar(max)) AS [text()] FROM #table where id >=@intial and  id<=@reset FOR XML PATH('') ), 1, 1, '' ) ;

 SELECT STUFF((SELECT ' ' + cast(data as nvarchar(max)) AS [text()] FROM #table where id in(select value from dbo.Split(@str1,',')) FOR XML PATH('') ), 1, 1, '' )  as advisor_id

 set @start=@start+1;
 if(@reset=4)
 begin
 set @reset=0;
 set @intial=@intial+1;
 if(@intial>@reset)
 begin
 set @reset=@intial;
end
 end
 else
 begin
 set @reset=@reset+1;
 end
 end

将结果存储在任何临时表中并选择全部



你做得更好存储过程：）

Store the Results in any Temp Table and Select All

better you make this as Stored Procedure :)

declare @s varchar(500) = 'Paul Cameron Adi Hardy'
set @s=@s+' '

declare @Table table (name varchar(100),leveling int,SNo int identity(1,1) PRIMARY KEY)

declare @ni int
set @ni = charindex(' ',@s,0)

;with cte (name,spaceindex,namenew,leveling)
as
(
	select substring(@s,0,@ni),@ni,substring(@s,@ni+1,LEN(@s)-1),1
	union all
	select substring(namenew,0,ni.ni),ni.ni,substring(namenew,ni.ni+1,LEN(namenew)-1),leveling+1
	from cte
		cross apply (select charindex(' ',namenew,0) ni)ni
	where charindex(' ',namenew,0)>0
)
insert into @Table
select name,leveling from cte

declare @MaxCount int,@MinCount int
select @MaxCount = max(leveling),@MinCount=min(leveling) from @Table

declare @Count int
set @Count=1

declare @tempName varchar(200)=null

while @MaxCount>0
begin
	if (select max(leveling) from @Table)=@Count-1
	begin
		set @tempName=null
		set @Count=@MinCount+1
		set @MinCount=@MinCount+1
		set @MaxCount=@MaxCount-1
	end
	
	select @tempName=isnull(@tempName+' ','')+name from @Table where SNo=@Count
	print @tempName
	
	select @Count=@Count+1
end

这篇关于如何通过分裂获得记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！