-- sample data
;WITH students (ID_Student, Name, rate) AS (
SELECT 1, CAST('Chipper Jones' AS nchar(30)), CAST('678*9' AS nchar(5)) UNION ALL
SELECT 2, 'Mike Piazaa', '98***' UNION ALL
SELECT 3, 'Barry Bonds', '678**' UNION ALL
SELECT 4, 'Larry Walker', '*88*9'
)
-- solution
SELECT
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 s
CROSS 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
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden