SELECT *, CASE WHEN SUBSTRING(rate, 1, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 1, 1) END AS game1, CASE WHEN SUBSTRING(rate, 2, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 2, 1) END AS game2, CASE WHEN SUBSTRING(rate, 3, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 3, 1) END AS game3, CASE WHEN SUBSTRING(rate, 4, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 4, 1) END AS game4, CASE WHEN SUBSTRING(rate, 5, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 5, 1) END AS game5FROM students

; WITH cte_Students AS( SELECT *, CASE WHEN SUBSTRING(rate, 1, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 1, 1) END AS game1, CASE WHEN SUBSTRING(rate, 2, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 2, 1) END AS game2, CASE WHEN SUBSTRING(rate, 3, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 3, 1) END AS game3, CASE WHEN SUBSTRING(rate, 4, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 4, 1) END AS game4, CASE WHEN SUBSTRING(rate, 5, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 5, 1) END AS game5 FROM students)SELECT *, ( game1 + game2 + game3 + game4 + game5 ) / 5 AS [percent]FROM cte_Students

; WITH cte_Students AS( SELECT *, CASE WHEN SUBSTRING(rate, 1, 1) = '*' THEN 10 /*The previous version had apostrophes here which made the data type of the column as VARCHAR*/ ELSE SUBSTRING(rate, 1, 1) END AS game1, CASE WHEN SUBSTRING(rate, 2, 1) = '*' THEN 10 ELSE SUBSTRING(rate, 2, 1) END AS game2, CASE WHEN SUBSTRING(rate, 3, 1) = '*' THEN 10 ELSE SUBSTRING(rate, 3, 1) END AS game3, CASE WHEN SUBSTRING(rate, 4, 1) = '*' THEN 10 ELSE SUBSTRING(rate, 4, 1) END AS game4, CASE WHEN SUBSTRING(rate, 5, 1) = '*' THEN 10 ELSE SUBSTRING(rate, 5, 1) END AS game5 FROM students)SELECT *, ( game1 + game2 + game3 + game4 + game5 ) / 5.0 /*Made 5 as 5.0 to avoid integer conversion*/ AS [percent]FROM cte_Students

-- sample data;WITH students (ID_Student, Name, rate) AS (SELECT 1, CAST('Chipper Jones' AS nchar(30)), CAST('678*9' AS nchar(5)) UNION ALLSELECT 2, 'Mike Piazaa', '98***' UNION ALLSELECT 3, 'Barry Bonds', '678**' UNION ALLSELECT 4, 'Larry Walker', '*88*9' )-- solutionSELECT s.ID_Student, s.Name, s.rate, x.game1, x.game2, x.game3, x.game4, x.game5, (x.game1 + x.game2 + x.game3 + x.game4 + x.game5) / 5.0 AS [percent]FROM students sCROSS APPLY (SELECT Game1 = CAST(REPLACE(SUBSTRING(rate, 1, 1),'*','10') AS INT), Game2 = CAST(REPLACE(SUBSTRING(rate, 2, 1),'*','10') AS INT), Game3 = CAST(REPLACE(SUBSTRING(rate, 3, 1),'*','10') AS INT), Game4 = CAST(REPLACE(SUBSTRING(rate, 4, 1),'*','10') AS INT), Game5 = CAST(REPLACE(SUBSTRING(rate, 5, 1),'*','10') AS INT)) x