How Can I Get Record By Spliting

Question

0.00/5 (No votes)

See more:

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 want a work concate in sequence of each combination in sequence.
how can i accomplish . please suggest me.
Thanks

Posted 27-Nov-14 20:26pm

PKriyshnA

Add a Solution

Comments

Mehdi Gholam 28-Nov-14 2:42am

Why?

PKriyshnA 28-Nov-14 4:07am

To search for "Is Containing In"

Maciej Los 28-Nov-14 11:42am

?

King Fisher 28-Nov-14 12:05pm

homework for us :) :)

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

yusuf_ahmed · Answer 1 · 2014-11-28T02:24:00

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

King Fisher · Answer 2 · 2014-11-27T23:25:00

Run this Functions:

SQL

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

SQL

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 :

SQL

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 :)

syed shanu · Answer 3 · 2014-11-27T20:52:00

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

SQL

    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

How Can I Get Record By Spliting

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0