Post reply

combine multiple lines into a single line in a column

  • October 18, 2017 at 6:47 am

    #325891

    I need help please
    In my table I have column called LongDescription it is of type ntext. The texts on this column is more that 2 lines. I need to create an extra column and put the text in one line but use some characters e.g. || where the line is more than one.
    the column looks like this

    These inspections and readings are carried out daily and readings recorded on inspection sheet.
    Monday
    Tuesday
    Wednesday
    Thursday
    Friday

    and the output I was is:
    These inspections and readings are carried out daily and readings recorded on inspection sheet. || Monday || Tuesday || Wednesday || Thursday || Friday

    Thanks in Advance

  • October 18, 2017 at 7:03 am

    #1964198

    Use the REPLACE function. You'll need to work out what character is being used for line breaks - CHAR(13) or CHAR(10), maybe.

    REPLACE(MyCol,CHAR(13),'||')

    John

  • October 18, 2017 at 8:46 am

    #1964228

    Thank John
    it did work.

  • October 18, 2017 at 8:53 am

    #1964230

    John Mitchell-245523 - Wednesday, October 18, 2017 7:03 AM

    Use the REPLACE function. You'll need to work out what character is being used for line breaks - CHAR(13) or CHAR(10), maybe.

    REPLACE(MyCol,CHAR(13),'||')

    John

    nText columns can't be used in REPLACE function, may need to use Cast to varchar or nvarchar. But if size will be more than 4000 for nvarchar or 8000 for varchar than also it these string manipulation functions will not work.

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

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