json-extract sqlite format

Question

I used the following command SELECT json_extract(data,'$.address') FROM data;

and output as CSV file.

Output in CSV file is

Field (column) in CSV file is saved as 2 lines for 1 field (column).

Eg-

"71 CHOA CHU KANG LOOP

NORTHVALE"

How could I save field(column) as 1 line ?

That is I don't want to include new line character in filed(column).

Eg-

"71 CHOA CHU KANG LOOP NORTHVALE"

Thanks.

score 0 · Accepted Answer · answered Dec 27 '19 at 17:31

0

Just replace the new line character:

select replace(json_extract(data,'$.address'), char(10), '') from data;

This will catch the newline character ('\n'). If you want '\r' and '\r\n' too:

select replace(
    replace(json_extract(data,'$.address'), char(10), ''), 
    char(13),
    ''
) from data;

answered Dec 27 '19 at 17:31

GMB

Thanks so much, How could I replace **comma** (,) ? – kkk Dec 27 '19 at 18:23
@kkk: welcome; just extend the expression with more nested `replace()`, as showned in my second query. – GMB Dec 27 '19 at 18:24
Thanks GMB, got it, I need to use **char(44)** – kkk Dec 27 '19 at 18:36
,how could I remove double quote and \n at above ? The result I'd like to be 71 CHOA CHU KANG LOOP NORTHVALE without double quote and \n – kkk Jan 04 '20 at 00:42

1 Answers1