Using the Stuff function we can do.The STUFF string function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
DECLARE @FullName VARCHAR(100);
DECLARE @Alias VARCHAR(20);
SET @FullName = 'Clark Kent'
SET @Alias = ' "Superman" '
SELECT STUFF(@FullName, CHARINDEX(' ', @FullName), 1, @Alias) AS [FullName]
Output
Clark "Superman" Kent