-2

enter image description hereI have table having Column "Title" having multiple String value with comma I want to split it with comma & move to next line in same column is that possible with SQL SERVER.i want it in same row but next line.

Eg: Test1,Test2,Test3,Test4

Expected:

Test1
Test2 
Test3
...
Wenfang Du
  • 8,804
  • 9
  • 59
  • 90
Shilpi
  • 25
  • 2
  • 12
  • A search on this site for "SQL split" should give you at least a dozen answers to this question. – R. Richards Oct 24 '17 at 09:42
  • https://social.technet.microsoft.com/wiki/contents/articles/26937.t-sql-splitting-a-string-into-multiple-columns.aspx – Stanislav Kundii Oct 24 '17 at 09:43
  • First, read [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutely yes!** Then, If you are using version 2016 or higher, there is a built in string_split function. If not, read Aaron Betrand's [Split strings the right way – or the next best way](https://sqlperformance.com/2012/07/t-sql-queries/split-strings). – Zohar Peled Oct 24 '17 at 09:53
  • Thanks for your reply .I don't have "String_split" in built function in SQL server & also i go through what link you provided there they creating function for spliting Cant we do without function. &also i want it to split in same row but next line,not in next row. – Shilpi Oct 24 '17 at 10:29

3 Answers3

0

This code may helps you

IF OBJECT_ID('tempdb..#t') IS NOT NULL
DROP TABLE #t
CReate table #t (Title nvarchar(1000))

INSERT INTO #t
SELECT 'Test1,Test2,Test3,Test4'

SELECT 
SPlit.a.value('.','nvarchar(1000)') AS Title FROM
(
SELECT 
CAST('<S>'+REPLACE(Title,',','</S><S>')+'</S>' AS XML ) AS Title FROM #t
)A
CROSS APPLY Title.nodes('S') AS SPlit(a)

Result

Title
-----
Test1
Test2
Test3
Test4
Sreenu131
  • 2,476
  • 1
  • 7
  • 18
  • Thanks for reply I tried what you provided, what is happening every split value is showing in next row but i want it one row but next line.please see the above screenshot it showing ever split value in next row.what i need is same row but next line. – Shilpi Oct 24 '17 at 10:03
0

Yes it is possible. You can create a function first to achieve it. Here is the function you can use :

CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT, @temp varchar(max)
    set @temp = ''
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        SET @temp = @temp + CHAR(13)+CHAR(10) + SUBSTRING(@string, @start, @end - @start)
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)        
    END 

    INSERT INTO @output (splitdata)  
    VALUES(@temp) 

    RETURN 
END

And then you can make the query like below :

select *from dbo.fnSplitString('Test1,Test2,Test3,Test4',',')
Md. Suman Kabir
  • 5,243
  • 5
  • 25
  • 43
  • Thanks for reply can we do it without function & also i want it to split in same row but next line not in next row. – Shilpi Oct 24 '17 at 10:32
  • It's tough to do this on the fly. I updated the function to get your desired result, please check. It will still show in one line as sql server does not show linebreaks in cells, but if you copy the content and use elsewhere, you will see they are separated by new lines. – Md. Suman Kabir Oct 24 '17 at 10:41
  • Is that any other way without creating a function for it. – Shilpi Oct 24 '17 at 10:49
0

As you described it, you want the result to be in one field. So you do not want to split the value, but replace the , with a line break.

Select replace(title,',',char(13)+char(10))

SSMS does not show line breaks, but they are present in the result.

Peter
  • 850
  • 5
  • 16