Split one field string with a delimitter into multiple fields

[code green]

I am migrating data from DB1 to DB2 via csv.
In DB1 there is only one address field 'address' that contains carriage returns between each address line.
DB2 however has three fields for the address going into the csv as Addr-01, Addr-02, Addr-03
How do I split this one field into three fields?
It has to be three even though DB1 address may have four or five 'lines' so any extra will just have to be concatenated into address3.

This is roughly the query

SELECT customer 'Account',
full_name Name, address 'Addr-01',
    --Addr-02,
    --Addr-03,
city 'Town',
county 'County',
postcode 'Post Code' FROM customer

I wrote a function that returned a table before I found it had to be used in a JOIN which meant a record for each address line. This is the function

FUNCTION splitString (@delim CHAR(1), @str VARCHAR (100))
RETURNS @t TABLE (line VARCHAR(50))
AS
BEGIN
DECLARE @pos INT
DECLARE @piece VARCHAR(50)

IF RIGHT(RTRIM(@str),1) <> @delim
SET @str = @str + @delim

SET @pos = CHARINDEX(@delim, @str)
WHILE @pos <> 0
BEGIN
SET @piece = LEFT(@str, @pos - 1)

INSERT INTO @t VALUES(CAST(@piece AS VARCHAR(50)))

SET @str = STUFF(@str, 1, @pos, '')
SET @pos = CHARINDEX(@delim , @str)
END
RETURN
END

[Uncle Dickie]

The function below may get what you are after, it works on a very limited test I have done. There is no error trapping in the function so if there are not enough carriage returns (i.e. at least 2) it may have a fit!:

FUNCTION MySplit(@Line int, @str nvarchar(255))
RETURNS nvarchar(255)
 
AS
BEGIN
 
DECLARE    @Output nvarchar(255)
 
SET @Output = CASE    @Line
                WHEN 1 THEN    substring(@Str,1,CHARINDEX(char(13), @str))
                WHEN 2 THEN substring(@Str,CHARINDEX(char(13), @str)+1,charindex(char(13),(substring(@str,charindex(char(13),@str)+1,255))))
                WHEN 3 THEN substring(@Str,
                        len(substring(@Str,1,CHARINDEX(char(13), @str)))
                        + 2 +
                        len(substring(@Str,CHARINDEX(char(13), @str)+1,charindex(char(13),(substring(@str,charindex(char(13),@str)+1,255))))),
                        255)
                ELSE    'Not defined'
            END
 
RETURN @Output
END

you would then need to call the function 3 times passing the line variable you are after:

SELECT   MySplit(1,Address1) AddressLine1
        ,MySplit(2,Address1) AddressLine2
        ,MySplit(3,Address1) AddressLine3
        ,MySplit(4,Address1) JustTest
        ,Address1
FROM        MyTable