There isn't proper way to store numbers as a text.
If you want to extract numbers from text, please try this:
DECLARE @tmp TABLE (UserID VARCHAR(30))
INSERT INTO @tmp (UserID)
VALUES('1,2,3,4,5,6'), ('1,4,5,6'), ('1')
;WITH MyCTE AS
(
SELECT 1 AS RowNo, UserID AS NewUserId, NULL AS Remainder
FROM @tmp
WHERE CHARINDEX(',',UserID)=0
UNION ALL
SELECT 2 AS RowNo, LEFT(UserID,CHARINDEX(',',UserID)-1) AS NewUserId, RIGHT(UserID,LEN(UserID) - CHARINDEX(',', UserID)) AS Remainder
FROM @tmp
WHERE CHARINDEX(',',UserID)>0
UNION ALL
SELECT RowNo+1 AS RowNo, LEFT(Remainder,CHARINDEX(',',Remainder)-1) AS NewUserId, RIGHT(Remainder,LEN(Remainder) - CHARINDEX(',', Remainder)) AS Remainder
FROM MyCTE
WHERE CHARINDEX(',',Remainder)>0
UNION ALL
SELECT RowNo+1 AS RowNo, Remainder AS NewUserId, NULL AS Remainder
FROM MyCTE
WHERE CHARINDEX(',',Remainder)=0
)
SELECT RowNo, CONVERT(INT,NewUserID) AS NewUserID, Remainder
FROM MyCTE
Result:
RowNo NewUID Remainder
1 1 NULL
2 1 2,3,4,5,6
2 1 4,5,6
3 4 5,6
4 5 6
5 6 NULL
3 2 3,4,5,6
4 3 4,5,6
5 4 5,6
6 5 6
7 6 NULL