convert comma seperated values into rows (SQL Developer)

Short Description :           Script describe one of the way to divide values into rows by using any dividing value.

Dividing any data value into different record by using any dividing criteria is always been interesting, today I am going to describe one of many ways to divide any values into rows by using any criteria , In this blog, I will explain how to divide a comma “,” separated value into rows and then as an extension we can add “-“ also as a dividend.

Let’s create a Table and insert some dummy records for Demo.

CREATE TABLE [dbo].[department](

      [dept_id] [numeric](18, 0) NULL,

      [sub_dept_id] [varchar](5000) NULL

) ON [PRIMARY]

GO

Inserting Dummy Records

INSERT INTO DBO.department VALUES(1,'1001,1002,1003-1004,1005,1006,1007-1009')

INSERT INTO DBO.department VALUES(2,'2001-2002,2003,2004,2005,2006,2007,2008, 2009-2010')

Select inserted records 

By Running Script for Comma Seperated values :

Here you can see script have divided values into rows by using comma seperated method , but still record with " - " are togather. If I want to seperate these records also then I will use previous scripts values and by using those values i'll again purify the result but this time criteria will be " - ".

In above mentioned code you can see we used same script by changing divident criteria from " , " to " - ", and FROM clause from actual table name to "Already fetched data" which is TEMP.

By using this mehtod you can add as many criteria's as your requirement.

Other Developer related blogs
table data comparison
sqlcmd export data to txt
sql analytical functions
script to generate foreign keys
getting LEAD and LAG values manually