ChrisM@Work (6/13/2013)

---

-- 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

Thanks for your kind reply .

But the result show me something like 8.600000

I would like it returns 8.6

Thanks in advance.