How to replace a newline character with row number in Postgres / sql?

user_7642507 0
Viewed 57

How to replace a new line character with row number within a string based on id following is the sample from one row from a table ,table has so many rows and each row should starts with 1.and so on.

sample data 
I
am
Awesome 

desired out put 
1.I 
2.am 
3.Awesome  

I tried to replace newline with rownumber but no success

select concat(1.,replace(field,char(10),cast(1+row_number()over(order by field) as varchar),'.') as desired_Formula from tbl

any help or suggestions are welcomed , It should be ideal if it's done without using cte.

postgresql sql
1 Answers

One option would be to create an array based on the newline character, then UNNEST .. WITH ORDINALITY, so that you have a row number, and from there turn it again into a single row with string_agg:

SELECT string_agg(id || '.' || word,E'\n')
FROM tbl
CROSS JOIN LATERAL 
  UNNEST(string_to_array(sample,E'\n')) WITH ORDINALITY j (word,id)
GROUP BY sample;

 string_agg 
------------
 1.I       +
 2.am      +
 3.Awesome
(1 Zeile)

If you want to split the items in many rows, just get rid of the string_agg and the GROUP BY clause:

SELECT id || '.' || word
FROM tbl
CROSS JOIN LATERAL 
  UNNEST(string_to_array(sample,E'\n')) WITH ORDINALITY j (word,id);

 ?column?  
-----------
 1.I
 2.am
 3.Awesome
(3 Zeilen)

Demo: db<>fiddle

user_22753880
Related