locked
carriage returns RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I currently have a table that contains an id and an address field. This address field contains the name of the user and the address.

    I want to split this table into 3 fields id, name, and address.

    the problem i have is that the address field contains carriage returns. To split the fields quickly i am using excel to generate updates based on an export. This export is generated using a select statement however i am unable to determine where these carriage returns are based on this export. IS there a way to have the export contain this data or at the very least what the update statement would look like that could add a CR to column

     

    any advice would be brilliant.

     

    Tuesday, April 27, 2010 10:06 PM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    try

    select Address, replace(Address, char(13), char(10)) as NewAddress from myTable and export to Excel. Check how the NewAddress will display.

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, April 27, 2010 10:54 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    The question is not very clear. To add CR to a column using update you can do

    update myTable set Address = Address + char(13) -- in Excel we use char(10) though

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, April 27, 2010 10:23 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    The question is not very clear. To add CR to a column using update you can do

    update myTable set Address = Address + char(13) -- in Excel we use char(10) though

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

    Sorry its one of those wierd scenarios to explain.

    That does answer part of my question.

    The other part is that I want to get the current data from an sql server 2000 database into excel so that i can see where the carriage returns are in the data. currently if i just do a select from the table and export it to excel the address field is shown as a single column with no CR's

    does that make more sense? 

    Thanks for your help

    Tuesday, April 27, 2010 10:50 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    try

    select Address, replace(Address, char(13), char(10)) as NewAddress from myTable and export to Excel. Check how the NewAddress will display.

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, April 27, 2010 10:54 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    try

    select Address, replace(Address, char(13), char(10)) as NewAddress from myTable and export to Excel. Check how the NewAddress will display.

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Nice idea, unfortunately excel still displays them as single lines of data with no indications as to where the char(10)/char(13)'s should be.
    Wednesday, April 28, 2010 6:50 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hii..

    How you are Exporting the Results into Excel ?

    Using Copy  and Paste or using OpenDataSource Functionality ??

     

    Best Regards,
    Gopi V

    If you have found this post helpful, please click the Vote as Helpful link (the green triangle and number on the top-left).

    If this post answers your question, click the Mark As Answered link below. It helps others who experience the same issue in future to find the solution.

    Wednesday, April 28, 2010 9:50 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Are you sure there are char(10) at all in your Address field?

    Try

    select len(NewAddress) - len(NewAddress, replace(NewAddress,char(10),'')) as TotalLFs

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, April 28, 2010 12:00 PM