Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Location
    SA
    Posts
    25

    Unanswered: New line in concatenated field

    HI
    I have a query which populates a field by concatenating few other fields. For each field concatenated, the value should be displayed in a new line.

    An example
    SELECT 'INSERT INTO Table1(
    Title) values
    (''Name: ' + ForeName + ' Surname: ' + SurName + ' Title: ' + Title +);'
    FROM Table2
    This returns
    INSERT INTO Table1 (title) values (Name: XX SurName: YY Title: ZZ)

    Is there a way enough space is created between each concatenated field so that they get displayed in new lines
    Name: XX
    SurName: YY
    Title: ZZ

    rather than Name: XX SurName: YY Title: ZZ

    The problem is the query is run in Query analyzer to generate the insert statements and these insert statements then are run in Oracle.

    Char (13) does not seem to help.

    Thanks
    Nimisha

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: New line in concatenated field

    Originally posted by Nimisha
    HI
    I have a query which populates a field by concatenating few other fields. For each field concatenated, the value should be displayed in a new line.

    An example
    SELECT 'INSERT INTO Table1(
    Title) values
    (''Name: ' + ForeName + ' Surname: ' + SurName + ' Title: ' + Title +);'
    FROM Table2
    This returns
    INSERT INTO Table1 (title) values (Name: XX SurName: YY Title: ZZ)

    Is there a way enough space is created between each concatenated field so that they get displayed in new lines
    Name: XX
    SurName: YY
    Title: ZZ

    rather than Name: XX SurName: YY Title: ZZ

    The problem is the query is run in Query analyzer to generate the insert statements and these insert statements then are run in Oracle.

    Char (13) does not seem to help.

    Thanks
    Nimisha
    Try CHR(10) instead of CHR(13):

    SQL> select ename||chr(10)||ename
    2* from emp;

    ENAME||CHR(10)||ENAME
    ---------------------
    SMITH
    SMITH

    ALLEN
    ALLEN

    BTW, your insert syntax is wrong, should be:

    INSERT INTO table1 (title) VALUES ('Name: XX SurName: YY Title: ZZ');

  3. #3
    Join Date
    Mar 2003
    Location
    SA
    Posts
    25

    New line between columns when concatenated

    Hi Tony

    Ta for the reply. I'll try it out. Got few issue @ the mo with my app, so can't test it.

    N

  4. #4
    Join Date
    Jul 2003
    Location
    US
    Posts
    314
    Hi
    Good that I came across this solution. I would surely be using the same in near future, and good for the headsup.

    By the way, does anyone of you know as to how to search a able on a CLOB object.

    I would appreciate ur response.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •