SQL Server Remove \t\n FROM JSON

nbysray5  于 2023-03-11  发布在  其他
关注(0)|答案(1)|浏览(135)

I have a table that store email body

I extract into json format in a store procedure

"emailTexts": [
  {
    "id": 1,
    "body": "Dear Sir\r\n Greetings!\r\n ThankYou",
  }

How can I remove this, at the same time, i will need to send the same format to another system

bvjveswy

bvjveswy1#

I am not really sure you would want to do such a thing. Anyway, you could use Replace(). ie:

DECLARE @t VARCHAR(MAX)
    = '"emailTexts": [
  {
    "id": 1,
    "body": "Dear Sir\r\n Greetings!\r\n ThankYou",
  }';

SELECT REPLACE(@t, '\r\n', '');

Or (to deal with all occurrences of \r \n \t):

SELECT REPLACE(REPLACE(REPLACE(@t, '\r', ''),'\n',''),'\t','');

PS: If you would do this on large data then consider creating a CLR function instead.

相关问题