[Solved] Comma-separation trouble

You can use

 SELECT   
 Split.r.value('.', 'VARCHAR(100)') AS Data  
 FROM  
 (
 SELECT CAST ('<M>' + REPLACE(roolno, ',', '</M><M>') + '</M>' AS XML) AS Data  
 FROM  table1
 ) AS r CROSS APPLY Data.nodes ('/M') AS Split(r);

or if you want to put where clause you can put it like this

 SELECT   
 Split.r.value('.', 'VARCHAR(100)') AS Data  
 FROM  
 (
 SELECT CAST ('<M>' + REPLACE(roolno, ',', '</M><M>') + '</M>' AS XML) AS Data  
 FROM  table1 where roolno='A001, V003, Z040'
 ) AS r CROSS APPLY Data.nodes ('/M') AS Split(r);