SQL select : splitting a field into multiple rows tokenized by char (13)

Question

I have a query as :

SELECT 'Item' AS TypeID, 
    iORCompID AS iEntityID,
    iORCompID AS iParentEntityID,
    '' as Source,
    vComments as Comment
FROM 
    OrderResultComponents
WHERE IOrderID = @IEntityID

The data i get is following

TypeID  |   iEntityID   |   iParentEntityID     |   Source  |   Comment
Item    |   1045        |   1045                |           |   Item Found  some pending comments \X000d\ by UserID1
Item    |   1027        |   1027                |           |   Item Found  with some pending comments \X000d\ by UserID2
Item    |   5389        |   5389                |           |   Item Found  with \X000d\ some \X000d\ pending comments \X000d\ by UserID1

(\X000d\ is Char(13) i guess)

However i want data to be like :

TypeID  |   iEntityID   |   iParentEntityID     |   Source  |   Comment
Item    |   1045        |   1045                |           |   Item Found  some pending comments
Item    |   1045        |   1045                |           |   by UserID1
Item    |   1027        |   1027                |           |   Item Found  with some pending comments 
Item    |   1027        |   1027                |           |   by UserID2
Item    |   5389        |   5389                |           |   Item Found  with 
Item    |   5389        |   5389                |           |   some
Item    |   5389        |   5389                |           |   pending comments 
Item    |   5389        |   5389                |           |   by UserID1

i.e. I want to split my Comment field with next line character of DB and repeat the other fields with this split...Any help???

EDIT: Ah, I got a hint from Split one column into multiple rows

Please correct my query if its not valid.

SELECT     
'Item' AS TypeID, '' as SetID,T.iORCompID , RIGHT(LEFT(T.vComments,Number-1),    
CHARINDEX(char(13),REVERSE(LEFT(char(13)+T.vComments,Number-1)))) 
FROM     master..spt_values,     OrderResultComponents T 
WHERE     Type = 'P' 
AND Number BETWEEN 1 
AND LEN(T.vComments)+1    
AND (SUBSTRING(T.vComments,Number,1) = char(13) ) AND T.IOrderID = @iEntityID

This breaks the logic of a database. Why do you want to do this? — Greg, Nov 18 '11 at 07:05
Actually my purpose is to select data from sql to write to file as it is (pritable ....) — Umer, Nov 18 '11 at 07:08
You need to do this with your application code. I assume you are using ASP.Net. Can you explain what you'd like the end product to be like? — Greg, Nov 18 '11 at 07:11
I am doing it under a windows service(.NET based) .. Actually it was intention to dump all the result to a text file so that the third party can receive that file, parse it and print without any change to formatting. — Umer, Nov 18 '11 at 07:16

score 0 · Answer 1 · edited May 23 '17 at 10:34

0

Without knowing any of the specifics about your application you will need to do something like this:

Query the database with your initial query and store it in a DataTable
Loop through the rows in the DataTable and concatenate your data with a StringBuilder
Write the contents of the StringBuilder to a text file

Check out this StackOverflow thread for information on how to create and write to a text file.

Please add a comment if you have any further questions.

edited May 23 '17 at 10:34

Community

1
1

answered Nov 18 '11 at 07:26

Greg

8,574
21
67
109

Greg, actually i don't have to do it in my application. obviously application/service can do it very easily. problem is, i have written a file writer that writes multiple items in the file. i mean the above query is a subset of items my SP returns. I cannot be specific to the items selected by my query. – Umer Nov 23 '11 at 07:40

Manish · Accepted Answer · 2011-11-19T07:12:07.660

declare @index int;
declare @TypeID varchar(10),@left varchar(max);
declare @iEntityID int,@iParentEntityID int;
declare @Source varchar(max),@Comment varchar(max);

declare split_cursor cursor for 
select * from OrderResultComponents
open split_cursor
fetch next from split_cursor into @TypeID,@iEntityID,@iParentEntityID,@Source,@Comment
while (@@fetch_status=0)
begin
    set @index=charindex('\X000d',@Comment);
    while(@index!=0)
        begin
            set @left=substring(@Comment,1,@index-1);
            set @Comment=substring(@Comment,@index+8,len(@Comment));
            insert into #split values (@TypeID,@iEntityID,@iParentEntityID,@Source,@left);
            set @index=charindex('\X000d',@Comment);
        end
        insert into #split values (@TypeID,@iEntityID,@iParentEntityID,@Source,@Comment);
    fetch next from split_cursor into @TypeID,@iEntityID,@iParentEntityID,@Source,@Comment
end
select * from OrderResultComponents;
select * from #split;
close split_cursor
deallocate split_cursor
truncate table #split

SQL select : splitting a field into multiple rows tokenized by char (13)

2 Answers2