Post reply

Carriage Return in SQL Server

  • 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??

  • November 18, 2014 at 5:06 am

    #1759220

    Use a splitter function.

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

    -- Gianluca Sartori

  • November 18, 2014 at 5:14 am

    #1759225

    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!

  • November 18, 2014 at 5:14 am

    #1759227

    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

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

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