Separating data from a column with multiple char(13) line breaks into their own columns

Question

The field is called fmstreet when the field displays in SSRS it contains the managers name beginning with ATTN: Store Manger, street address 1, and street address 2,. The way the data looks in SSMS is ATTN: Brian Adams 343 Albert Ave 17th Street. This displays the field in SSRS

ATTN: Brian Adams
343 Albert Ave
17th Street

I've been able to get the first line into it's own column but don't know exactly how to do the other two. Also some rows only have one street address not two.

SELECT somast.fsono, 
somast.fcustno,
somast.fcontact,
soship.fmstreet,
Case When cast(soship.fmstreet AS CHAR(100)) like 'ATTN:%'
Then LEFT(cast(soship.fmstreet AS CHAR(100)),CHARINDEX(CHAR(13),cast(soship.fmstreet AS CHAR(100))))
End as 'Attention'
FROM  soitem
INNER JOIN somast ON soitem.fsono = somast.fsono
LEFT OUTER JOIN oship ON somast.fsono = soship.fcsono AND soship.fcenumber = ''
WHERE  (somast.fstatus <> 'Cancelled') AND (somast.fsocoord = 'IFP'
OR somast.fsocoord = '711');

Answer 1

I think your best option here is to look into using the Split function in SSRS and you can split the string based on char(13), I think. This post seems like it would direct you correctly, I would definitely check for the value's existance like it suggests.

ssrs expression to split string possible?