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

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.

CodePudding user response：

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:

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