CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
user3269841
user3269841

Reputation: 3

Split text value insert another cell

I want split text from NAME column and insert comma separated data to PARCA column for each row. ex:

name            parca
----            -------------
john            j,jo,joh,john

Code:

DECLARE @i int = 0
WHILE @i < 8
BEGIN 
SET @i = @i + 1
update export1 set PARCA = cast ( PARCA as nvarchar(max))  + cast (substring(NAME,1,@i) as nvarchar(max) ) +',' 
FROM export1 
end

There are two things I can't do;

  1. I could not equalize the @i value to name row count
  2. I could not checked NAME column whether the value in PARCA column

Upvotes: 0

Views: 178

Answers (4)

sureshhh
sureshhh

Reputation: 1216

DECLARE @Count INT,@I INT
SET @I = 1
SET @Count = LEN('SURESH')
DECLARE @N VARCHAR(2000)
SET @N = ''
WHILE @Count > 0
BEGIN
    SET  @N = @N + ','+SUBSTRING('SURESH',1,@I)
    SET  @I = @I+1
    SET @Count  = @Count -1
END

SELECT SUBSTRING(@N,2,2000)

The above code is only a sample.'SURESH' is your name field.from which you can pass your own name values.Instead of final select u can put ur update.

Upvotes: 1

t-clausen.dk
t-clausen.dk

Reputation: 44336

Create this function:

create function f_parca
(
 @name varchar(100)
) returns varchar(max)
as
begin
declare @rv varchar(max) = ''

if @name is not null
select top (len(@name)) @rv += ','+ left(@name, number + 1) 
from master..spt_values v
where type = 'p'

return stuff(@rv, 1,1,'')
end

Testing the function

select dbo.f_parca('TClausen')

Result:

T,TC,TCl,TCla,TClau,TClaus,TClause,TClausen

Update your table like this:

UPDATE export1
SET PARCA = dbo.f_parca(name)

Upvotes: 1

Naveen Kumar
Naveen Kumar

Reputation: 1541

-- This query will give you exactly what you are looking for, use Emp Table with Ename as     column    

;with cte as
(
select  1 AS Counter,EName,CAST(SUBSTRING(E.EName, 1, 1) AS Varchar(100)) Name
        From EMP E 
union all 
select Counter+1,E.EName,CAST((Name + SUBSTRING(E.EName, Counter+1, 1))AS Varchar(100)) Name
From EMP E
     INNER JOIN CTE C ON C.Ename=E.EName
where Len(Name) < Len(E.EName) 
)
select EName AS Name,
   STUFF((    SELECT ',' + Name AS [text()]                        
                        FROM CTE A
                        WHERE
                        A.EName = cte.EName
                        FOR XML PATH('')
                        ), 1, 1, '' )                       
            AS Parca    
   from cte 
   Group By EName
   Order By EName
   option(MAXRECURSION 0)

Upvotes: 0

Naveen Kumar
Naveen Kumar

Reputation: 1541

Try this, this query will break the word into characters rows as expected then you can merge into a single row

DECLARE @Name AS Varchar(100)='Naveen'  

;with cte as
(
select 1 AS Counter,CAST(SUBSTRING(@Name, 1, 1) AS Varchar(100)) Name
union all 
select Counter+1,CAST((Name + ',' + SUBSTRING(@Name, Counter+1, 1))AS Varchar(100)) Name
from cte
where Len(Name) < Len(@Name) + (Len(@Name) -1)
)
select
    Name
   from cte
option(MAXRECURSION 0)

Upvotes: 0

Related Questions