Inserting Newlines in Text feld

  • I have this data

    SiteProductReleaseTypeCnt

    Company NameProduct12005Upgrade1

    Company NameProduct12006Upgrade2

    Company NameProduct12007New1

    Company NameProduct22002New1

    Company NameProduct22004New1

    I am being asked to insert the following into a new a column in a new table. I am having difficulty in get this done and tested. WHen I use Char(10) + Char(13) + Char(9), I get a single line of text displayed. I apprecate any ideas (including a change to the presentation requirement).

    Site: Company Name

    Product: Product1

    Release: 2005

    Type: Upgrade : 1

    Release: 2006

    Type: Upgrade : 2

    Release: 2007

    Type: New : 1

    Product: Product 2

    Release: 2002

    Type: New : 1

    Release: 2004

    Type: New : 1

    Thanks,

    Bill

  • Hi

    At first sight I'd suggest you post your CREATE TABLE, INSERT and SELECT statements so that we can get a clearer picture of what you're trying to do.

    Regarding your column names, it looks like 'Type' and 'Cnt' should be separate columns but you have put the information for both columns together. Was this intentional?

    🙂

  • The tabs were lost in the initial message. Here are the statements to create table and test data:

    create table regs (

    site varchar(100),

    product varchar(100),

    release varchar(10),

    type varchar(10),

    cnt int

    )

    insert into regs values('Company Name','Product1','2005','Upgrade',1)

    insert into regs values('Company Name','Product1','2006','Upgrade',2)

    insert into regs values('Company Name','Product1','2007','New',1)

    insert into regs values('Company Name','Product2','2002','New',1)

    insert into regs values('Company Name','Product2','2004','New',1)

    Here is a look of the data where the initial character of each line is a period.

    Site: Company Name

    . Product: Product1

    . Release: 2005

    . Type: Upgrade : 1

    . Release: 2006

    . Type: Upgrade : 2

    . Release: 2007

    . Type: New : 1

    . Product: Product 2

    . Release: 2002

    . Type: New : 1

    . Release: 2004

    . Type: New : 1

    Thanks,

    Bill

  • Another attempt at showing the desired display. The .... would be replaced with tabs or spaces.

    The resulting delivery table is one row per company.

    Site: Company Name

    .....Product: Product1

    .......Release: 2005

    .........Type: Upgrade : 1

    .......Release: 2006

    .........Type: Upgrade : 2

    .......Release: 2007

    .........Type: New : 1

    ....Product: Product 2

    .......Release: 2002

    .........Type: New : 1

    .......Release: 2004

    ........Type: New : 1

  • If you are looking at the results in SSMS grid you will only get a single line. the Char(13) + Char(10) of ANSI (or is it ASCII?) new line will work.

    DECLARE @TestVar AS VARCHAR(50)

    SELECT

    @TestVar = 'Hello World' + CHAR(13) + CHAR(10) + 'This is a Second Line'

    PRINT @TestVar

    This will show in the messages window the output of the second line. A note, if you reverse the CHAR(13) + CHAR(10) to be CHAR(10) + CHAR(13) you will get 2 new lines.

    As to your design requirement, it seems arbitrary and not something that the DB is supposed to do... that is a presentation layer operation not a data layer, can it be done in SQL? yes. Should it? probably not. (just my $0.02)

  • Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

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