How to convert comma separated column to single value column - comma separate to value

select distinct value as crimecategory, lu.Description from Investigation iv cross apply string_split(iv.crimecategory,',') Left join Lookup lu on value = lu.LookupID where (CrimeCategory != '' OR CrimeCategory != NULL) AND BranchId=30 

or

 SELECT VALUE FROM string_split('1, 3, 5', ',')

reference:-  https://www.geeksforgeeks.org/how-to-turning-a-comma-separated-string-into-individual-rows-in-sql-server/