Breaking out data from a text field type

In my database there is a text field type that is used to enter street
address. This address could be a few lines long, each line with a
carriage return at the end.
Is there a way to search for these carriage returns and break out what
is in each line seperately?

Thanks.
Mike[posted and mailed, please reply in news]

Mike (mrea@.ohiotravelbag.com) writes:
> In my database there is a text field type that is used to enter street
> address. This address could be a few lines long, each line with a
> carriage return at the end.
> Is there a way to search for these carriage returns and break out what
> is in each line seperately?

Is that really the datatype text? That seems a bit over kill for a street
address. They would very rarely be over 8000 bytes. Or even 4000 if you
are using varchar.

The functions to use are substring and charindex. And char(13) for the
CRs. Or char(13) + char(10) if it's actually CR + LF. charindex does not
handle text beyond the varchar limit, but I don't think this would be
an issue.

You could also do:

SELECT @.adr = adr FROM tbl WHERE ..
SELECT str
FROM iter_charlist_to_table(@.adr, char(13))
ORDER BY listpos

You find this function on
http://www.sommarskog.se/arrays-in-...list-of-strings

Note that if you need to use char(13) + char(10) as delimiter, you
will have to change the function. (And not only the length of delimiter.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp