Carriage Return in SQL Server

Question

Post reply

Carriage Return in SQL Server

Conficker

SSCommitted

Points: 1567
More actions
November 18, 2014 at 4:30 am

#292195

Hi, would like to convert carriage return in to new fields??
can replace by comma or something else within same field, but want to put in separate fields.
eg. for address
Station Road
City Centre
London
EU1 1XA
Station Road, City Centre, London and EU1 1XA should got to different fields.
any help??

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Use a splitter function.

Here's a good one: http://www.sqlservercentral.com/articles/Tally+Table/72993/

-- Gianluca Sartori

Lowell SSC Guru Points: 323518 More actions · Answer 2

you also need to be aware of the number of fields in your splits...

if osmeone doesn't have a two part address, before the city, your fields could not line up the way you are thinking:

/*results

Item Item Item Item

Station Road City Centre London EU1 1XA

2012 SQL Street Fort Lauderdale,FL 33317 NULL

*/

with mySampleData

AS

(

select 'Station Road

City Centre

London

EU1 1XA' As RawAddress UNION ALL

SELECT '2012 SQL Street

Fort Lauderdale,FL

33317'

)

SELECT *

FROM mySampleData

outer APPLY (SELECT Item from dbo.DelimitedSplit8K(RawAddress,CHAR(13)) fn1 WHERE fn1.ItemNumber = 1) Addr1

outer APPLY (SELECT Item from dbo.DelimitedSplit8K(RawAddress,CHAR(13)) fn1 WHERE fn1.ItemNumber = 2) Addr2

outer APPLY (SELECT Item from dbo.DelimitedSplit8K(RawAddress,CHAR(13)) fn1 WHERE fn1.ItemNumber = 3) Addr3

outer APPLY (SELECT Item from dbo.DelimitedSplit8K(RawAddress,CHAR(13)) fn1 WHERE fn1.ItemNumber = 4) Addr4

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

I'd recommend DelimitedSplit8K as well. If you want to split on the carriage return, split by CHAR(10). Splitting by a comma is simply ','.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions