splitting fields into separate elements

[scotent]

I have a set of records (company details) exported from Outlook. The address feild is a concatenation of the seperate address lines. This means that instead of the usual address1, address2 etc it all appears in the address1 feild. the actual alements fo the address are de;imited by a carriage return which appears as to thick black vertical lines in the excel column.

Question, in terms of SQLwhat is the easiest way to run a query to split the field into three. The address is delimitted by a carriage return.

"Pentland View House
Damhead
Lothianburn"

TIA

Pentland View House¦¦Damhead¦¦Lothianburn

 

[jhall156]

I think this one depends on the flavor of SQL you're using. In T-SQL (MSSQL Server) you could try
SELECT LEFT(thefield,CHARINDEX(CHAR(13),thefield)-1) as firstline,
etc.

then substring with charindexes for the next etc.
that is assuming the CR is CR and CR+LF or someother ASCII combination.

Hope this spurs some ideas for you. JHall

 

[scotent]

Hi

Thanks for that, I ,have wimped out. As I have only a couple of hundred records it may well be less time consumeing to do it by hand. Luddite I know but time is an issue.

Thanks for your suggestions tho