 <p>try this:</p> <pre><code>DECLARE @YourTable table (RowID int, Layout varchar(200)) INSERT INTO @YourTable select ROW_NUMBER() over (order by (select 0)) as rn,replace(RIGHT(data,len(data)-CHARINDEX('_',data,1)),'_',',') from temptab ;WITH SplitSting AS ( SELECT RowID,LEFT(Layout,CHARINDEX(',',Layout)-1) AS Part ,RIGHT(Layout,LEN(Layout)-CHARINDEX(',',Layout)) AS Remainder FROM @YourTable WHERE Layout IS NOT NULL AND CHARINDEX(',',Layout)&gt;0 UNION ALL SELECT RowID,LEFT(Remainder,CHARINDEX(',',Remainder)-1) ,RIGHT(Remainder,LEN(Remainder)-CHARINDEX(',',Remainder)) FROM SplitSting WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)&gt;0 UNION ALL SELECT RowID,Remainder,null FROM SplitSting WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)=0 ) SELECT distinct cast(part as int) FROM SplitSting where len(part) &gt; 0 order by cast(part as int) </code></pre>