Split text value insert another cell

Question

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;

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

What do you mean by cell? Database column?

Liath
– Liath

2014-02-04 07:54:56 +00:00
Commented Feb 4, 2014 at 7:54 — Liath
– Liath, Commented Feb 4, 2014 at 7:54

t-clausen.dk · Accepted Answer · 2014-02-04 13:35:03Z

1

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)

edited Feb 4, 2014 at 13:35

answered Feb 4, 2014 at 10:13

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user3269841 Over a year ago

thank you for your interest. how can use this function as foreach parca column update? is there any idea?

t-clausen.dk Over a year ago

@user3269841 yes, read last 2 code lines, that will update all rows

user3269841 Over a year ago

yes, i am seeing. but there is error Msg 1014, Level 15, State 1, Line 2 TOP clause contains an invalid value.

t-clausen.dk Over a year ago

@user3269841 I think you have a null value in your table. I have taken that into account now

user3269841 Over a year ago

yes. i have null value in my table. can i skipping null values?

|

Abraham Hamidi · Accepted Answer · 2014-02-17 03:04:39Z

1

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.

edited Feb 17, 2014 at 3:04

Abraham Hamidi

13.9k3 gold badges39 silver badges52 bronze badges

answered Feb 4, 2014 at 8:45

sureshhh

1,21611 silver badges16 bronze badges

5 Comments

Semih Yagcioglu Over a year ago

+1 but, eliminating the first comma may be better from @N instead of using substring.

Jayvee Over a year ago

I suppose each name will have a different length... this will solve only part of the problem. The table has to be read through a cursor.

sureshhh Over a year ago

@semihyagcioglu How you eliminate forst comma without using substring?

Semih Yagcioglu Over a year ago

Sorry for the confusion, substring is OK, but CHARINDEX may be a better fit though.

user3269841 Over a year ago

Thanks for your replies, so, how can i for each column update? not as varchar, as returned sql row group

Naveen Kumar · Accepted Answer · 2014-02-04 08:54:13Z

0

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)

answered Feb 4, 2014 at 8:54

Naveen Kumar

1,54110 silver badges12 bronze badges

Comments

Naveen Kumar · Accepted Answer · 2014-02-04 09:27:51Z

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

Collectives™ on Stack Overflow

Split text value insert another cell

4 Answers 4

6 Comments

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related