I have one col in my table like:
There's probably a better way to do it that this, but this achieves what you're after:
DECLARE @email NVARCHAR(50) = 'Kiyara@ymail.com'; SELECT LEFT(@email, 1) + REPLICATE('X', LEN(SUBSTRING(@email, 2, CHARINDEX('@', @email) - 1))) + SUBSTRING(@email, CHARINDEX('@', @email), LEN(@email)); -- Result: KXXXXXX@ymail.com
This uses the REPLICATE() method, which according to the docs online starts with SQL Server 2008.
Repeats a string value a specified number of times.
REPLICATE ( string_expression ,integer_expression )