Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in August and this forum will be retired.

I was starting on a function to replace characters that json needs to be escaped. But sybase is to clever and replaces \n back into line break :(

The function: ALTER FUNCTION "DBA"."JsonEnc"(IN str LONG VARCHAR ) RETURNS LONG VARCHAR DETERMINISTIC BEGIN return replace(str,char(10),'\n') END

Test query: select 'aaa' || char(10) || 'bbb' as str, jsonenc(str) as jsonstr, replace(str,char(10),'\n') as test

Result: str | jsonstr | test aaa aaa aaa\nbbb bbb bbb

If the funtion replaces with say: |n it works as expected.

How do I get the function to not turn \n back to line break.

Or is there a simpler way to escape a json string?

asked 25 Aug '23, 03:57

Ove%20Halseth2's gravatar image

Ove Halseth2
accept rate: 0%

Hmm, the Result in the question did not survive formatting...

(25 Aug '23, 05:46) Ove Halseth2

I do not know for sure for Unicode, but for single-byte char sets, it should hold that CHAR(10) = '\n', so that isn't really a replacement.

select ascii('\n'), ascii(char(10)), ascii(char(13))

returns 10, 10, 13

And the FOR JSON clause will treat whitespace accordingly, without need for replacement AFAIK - see CHAR(10) and '\n' are handled identically:

select 'aaa' || char(10) || 'bbb\ncccc\\AfterTheBackspace' as strJson for json auto

returns [{"DUMMY":{"strJson":"aaa\nbbb\ncccc\AfterTheBackspace"}}]

Note: The real return has two adjacent baskslashes before the word "After", the page format won't show the second...

(25 Aug '23, 06:19) Volker Barth

Bah, return replace(str,char(10),'\\n')

Needs to escape the \

Not sure why the replace in the query does not need to escape the \

permanent link

answered 25 Aug '23, 05:44

Ove%20Halseth2's gravatar image

Ove Halseth2
accept rate: 0%

'\n' is a two-character string consisting of a backslash and an n. '\n' is identical to char(10) so replace( x, char(10), '\n' ) does nothing.

Perhaps the difference you see in the query might have to do with the client you are using to view the result? It could be displaying a character 10 as \n. Try using \n in the query and see if you get a result which is (visually) the same.

permanent link

answered 25 Aug '23, 07:17

John%20Smirnios's gravatar image

John Smirnios
accept rate: 37%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 25 Aug '23, 03:57

question was seen: 366 times

last updated: 25 Aug '23, 07:17