Finding longest matching prefix
One way (SQL Fiddle)

SELECT SName,
       SId,
       LEFT(SName, MAX(LEN(PName)))                                          AS PName,
       CAST(SUBSTRING(MAX(STR(LEN(PName), 10) + LTRIM(PId)), 11, 10) AS INT) AS PId
FROM   Sources S
       JOIN Prefixes P
         ON S.SName LIKE P.PName + '%'
GROUP  BY SId,
          SName 
ORDER BY SId   


I am assuming that none of the prefixes are allowed to contain characters such as % that would mess up a LIKE search.

It uses a variant of the method in listing 5 in this article on TOP N Per Group Queries to bundle the PId along with the MAX(length). Once the MAX(length) is known it can be used to re-calculate what the prefix must have been so there is no need to add PName in to the concatenation.