Extracting a specific amount of data from a field

I am working with a particular field in my SQL database that has the addresses of all our customers. Each part of the address is seperated with a Carriage return but is shown in the database as just a single line of text.
I need to show this in my report as a seperate field for each part of the address (A1, A2, A3 etc..) however I am unsure as to how to extract the data up to each carriage return and then from that one to the next one.
I can use the following which obviously just extracts the data up to the first specified character length but this doesnt really help very much.

SELECT LEFT(ADDRESS, X) AS A1
FROM GTW_BUSINESS_PARTNER

Has anybody got any ideas how this can be done.

Many thanks

From a performance point of view it is probably best, to perform the string splitting operation directly in the SQL statement or in a stored procedure.

If you want to do it with RDL expressions in the report, you could use the VB Split function as follows:

To get the first line: =Split(Fields!Address.Value, VbCrLf)(0)
To get the second line: =Split(Fields!Address.Value, VbCrLf)(1)

Notes:
* VbCrLf means Cr+Lf, you can also try VbCr if you only have Cr between the lines.
* The VB Split function will result in bad performance if used very often (e.g. called multiple times for thousands of rows in the report)

-- Robert

|||The SQL character (ascii) for carriage return is char(13) - char converts to ascii character..... does this point you in the right direction?