How to convert a string with multiple comma separated values to rows in SQL Server


Let’s we have below table structure:

create table Testdata(
SomeID int,
OtherID int,
Data varchar(max)
)

This table contain below sample records

insert Testdata select 1, 9, '18,20,22'
insert Testdata select 2, 8, '17,19'
insert Testdata select 3, 7, '13,19,20'
insert Testdata select 4, 6, ''

We want to get result like

1 9 18
1 9 20
1 9 22
2 8 17….

We can use below two options for this problem.
  • Using function and table variable 
CREATE FUNCTION dbo.Split
(
    @Line nvarchar(MAX),
    @SplitOn nvarchar(5) = ','
)
RETURNS @RtnValue table
(
    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    Data nvarchar(100) NOT NULL
)
AS
BEGIN
    IF @Line IS NULL RETURN
    DECLARE @split_on_len INT = LEN(@SplitOn)
    DECLARE @start_at INT = 1
    DECLARE @end_at INT
    DECLARE @data_len INT 
    WHILE 1=1
    BEGIN
        SET @end_at = CHARINDEX(@SplitOn,@Line,@start_at)
        SET @data_len = CASE @end_at WHEN 0 THEN LEN(@Line) ELSE @end_at-@start_at END
        INSERT INTO @RtnValue (data) VALUES( SUBSTRING(@Line,@start_at,@data_len) );
        IF @end_at = 0 BREAK;
        SET @start_at = @end_at + @split_on_len
    END
    RETURN
END
  
After this run below query to get desired result
 
select t.OtherID,x.Data
    from testData t
    cross apply (select Data from dbo.Split(t.Data,',') ) x
 
  • Using Sub query with cross apply
                     SELECT A.OtherID, 
                     Split.a.value('.', 'VARCHAR(100)') AS testData 
                     FROM 
                     (
                     SELECT OtherID, 
CAST ('<M>' + REPLACE(Data, ',', '</M><M>') + '</M>' AS XML) AS Data 
                     FROM  testData
                     ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a);
 

Comments

Post a Comment

Popular posts from this blog

How to Add HttpModule in MVC5 Application

Image
An HTTP module is an assembly that is called on every request made to your application. HTTP modules are called as part of the ASP.NET request pipeline and have access to life cycle events throughout the request. HTTP modules therefore give you the opportunity to examine incoming requests and take action based on the request. They also give you the opportunity to examine the outbound response and modify it. As MVC application is built over ASP.NET so we can create custom HttpModule in MVC application also. In this article we will create a simple HttpModule and register it in HttpApplication pipeline and verify that is loaded. Create a LoginModule Class in your MVC Application.   For creating HttpModule we have implement  IHttpModule interface which have two functions Dispose and Init. We have implemented Init method here. 2 .         In ASP.NET we were registering HttpModule in Web.Config. We can register module in MVC using Global.asax. Bel...
Read more

Map Routes using Route Class in MVC

In MVC application, we can register routes in RouteCollection using RouteConfig . This RouteConfig is called using Application_Start of application. RouteConfig is configured as below routes.MapRoute(                 name: "Default" ,                 url: "{controller}/{action}/{id}" ,                 defaults: new { controller = "ProgramConfigEditor" , action = "Index" , id = UrlParameter .Optional }             ); We can register route of the application using Route Class as below. After creation of Route collection, it will move to MvcRouteHandler object. Route route = new Route ( "{controller}/{action}/{id}" ,           ...
Read more