Semicolon, comma separated data to Rows

 If you have data in column in comma separated or semicolon separated and you would like to separate each one to a new row, it is pretty simple in SQL server. Use below query as per your requirement, right now this query is looking for semicolon as separator, and you modify the query as per requirement. Change the column names as per your table structure.

;WITH temp(ID, Newcolumn, Name) AS

(
    SELECT
        Id,
cast(LEFT(Name, CHARINDEX(';', Name + ';') - 1) as nvarchar(2000)),
        STUFF(Name, 1, CHARINDEX(';', Name + ';'), '')
    FROM 
YOURTABLENAME
where 
name like '%;%'
    UNION all
    SELECT
        Id,
cast(LEFT(Name, CHARINDEX(';', Name + ';') - 1) as nvarchar(2000)),
        STUFF(Name, 1, CHARINDEX(';', Name + ';'), '')
    FROM 
temp
    WHERE
        Name > ''
)
SELECT
    Newcolumn 
FROM 
temp 

Do not change the column name "Newcolumn" from query, as this column is key column for your query. will store all new data in this column.

how table data looks like for me:

YOURTABLENAME

id      Name

1       Andhra Pradesh; Arunachal Pradesh; Assam; Bihar; Chhattisgarh; Goa; Gujarat; Haryana; Himachal Pradesh; Jammu and Kashmir; Jharkhand; Karnataka; Kerala; Madhya Pradesh; Maharashtra; Manipur; Meghalaya; Mizoram; Nagaland; Odisha; Punjab; Rajasthan; Sikkim; Tamil Nadu; Telangana; Tripura; Uttar Pradesh; Uttarakhand; West Bengal; Andaman and Nicobar; Chandigarh; Dadra and Nagar Haveli; Daman and Diu; Lakshadweep; Delhi; Puducherry

2       Alabama;  Alaska;  American Samoa;  Arizona;  Arkansas;  California;  Colorado;  Connecticut;  Delaware;  District of Columbia;  Florida;  Georgia;  Guam;  Hawaii;  Idaho;  Illinois;  Indiana;  Iowa;  Kansas;  Kentucky;  Louisiana;  Maine;  Maryland;  Massachusetts;  Michigan;  Minnesota;  Minor Outlying Islands;  Mississippi;  Missouri;  Montana;  Nebraska;  Nevada;  New Hampshire;  New Jersey;  New Mexico;  New York;  North Carolina;  North Dakota;  Northern Mariana Islands;  Ohio;  Oklahoma;  Oregon;  Pennsylvania;  Puerto Rico;  Rhode Island;  South Carolina;  South Dakota;  Tennessee;  Texas;  U.S. Virgin Islands;  Utah;  Vermont;  Virginia;  Washington;  West Virginia;  Wisconsin;  Wyoming

How data will appear after executing query

1 Andhra Pradesh

1 Arunachal Pradesh

1 Assam

1 Bihar

1 Chhattisgarh

1 Goa

1 Gujarat

1 Haryana

1 Himachal Pradesh

1 Jammu and Kashmir

1 Jharkhand

1 Karnataka

1 Kerala

1 Madhya Pradesh

1 Maharashtra

1 Manipur

1 Meghalaya

1 Mizoram

1 Nagaland

1 Odisha

1 Punjab

1 Rajasthan

1 Sikkim

1 Tamil Nadu

1 Telangana

1 Tripura

1 Uttar Pradesh

1 Uttarakhand

1 West Bengal

1 Andaman and Nicobar

1 Chandigarh

1 Dadra and Nagar Haveli

1 Daman and Diu

1 Lakshadweep

1 Delhi

1 Puducherry

2 Alabama

2 Alaska

2 American Samoa

2 Arizona

2 Arkansas

2 California

2 Colorado

2 Connecticut

2 Delaware

2 District of Columbia

2 Florida

2 Georgia

2 Guam

2 Hawaii

2 Idaho

2 Illinois

2 Indiana

2 Iowa

2 Kansas

2 Kentucky

2 Louisiana

2 Maine

2 Maryland

2 Massachusetts

2 Michigan

2 Minnesota

2 Minor Outlying Islands

2 Mississippi

2 Missouri

2 Montana

2 Nebraska

2 Nevada

2 New Hampshire

2 New Jersey

2 New Mexico

2 New York

2 North Carolina

2 North Dakota

2 Northern Mariana Islands

2 Ohio

2 Oklahoma

2 Oregon

2 Pennsylvania

2 Puerto Rico

2 Rhode Island

2 South Carolina

2 South Dakota

2 Tennessee

2 Texas

2 U.S. Virgin Islands

2 Utah

2 Vermont

2 Virginia

2 Washington

2 West Virginia

2 Wisconsin

2 Wyoming