New line character

[nimishauk]

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

An exmaple
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

Char (13) does not seem to help.

Thanks
Nimisha

 

[sem]

I'm not sure whether + will work for concatenation (maybe it's processed by driver), but in Oracle || is used. I suppose that the number of quotes is also incorrect: inner quotes should be doubled. But if your code works against non-Oracle database, try to get the output as follows:

INSERT INTO Table1 (title) values ('Name: XX'|| chr(10)||'SurName: YY'|| chr(10)||'Title: ZZ')

P.S. Your target table violates 1st normal form.

Regards, Dima