Carriage Return in string

[Former Member]

Hi All

I am trying to replace a string with carriage return. How to use the carriage return in sap hana queries ?

eg,,

select 'firstname'||Char(13)||'lastname' from dummy;

I am expecting the result as

firstname

lastname

But the above query only results in firstnamelastname.

Please let me know how to include  a new line (Carriage return) inside a string?

Regards

Suman

[Rudy_Clement1 · Accepted solution]

Hi Suman,

I don't know if it's still relevant but I had a similar requirement where I needed to concatenate some texts, separate them by CRLF and store the result in a table. This is how it was done:

	DECLARE v_crlf NVARCHAR(2);
	SELECT BINTOSTR( HEXTOBIN('0D0A') ) --Carriage return line feed   INTO v_crlf   FROM DUMMY;

:v_crlf can now be used in select statements and logic to concatenate texts

Kind regards,

Rudy

[Former Member · Accepted solution]

Thank you Rudy . I Checked it and confirm that works.

I could not able to do this in procedure that time. I have added some place holders and replaced them with carriage returns in BODS

Regrads,

Suman.

[former_member480882 · Accepted solution]

This message was moderated.

[former_member675460]

I was able to remove the carriage returns using REPLACE_REGEXPR in this example:

DO

BEGIN 
DECLARE var1 NVARCHAR(20); 
var1 := 'This is a 
test';

SELECT
     :var1 
FROM
     DUMMY
UNION ALL
SELECT 
    REPLACE_REGEXPR('\n' IN :var1 with '' occurrence all)
FROM
     DUMMY;
END