DECLARE @isim        VARCHAR(100)
SET @isim = 'caner karadag'
 
SELECT SUBSTRING(@isim, 1, CHARINDEX(' ', @isim) - 1) AS [ad],
       SUBSTRING(@isim, CHARINDEX(' ', @isim) + 1, LEN(@isim) AS [soyad]