CODEWITHSUNDEEP
sqlxmlpostgresqlxsltnewline
yonetpkbji
yonetpkbji

Reputation: 1019

XML/SQL - Adding a string at the end of each line in individual fields

In Postgres I am trying to append a string to the end of each line of data in an individual field.

For example, I have the following database table called personal_details:

+-------+--------+-----------------------------------------------------+
|  name |   age  |                       details                       |
+-------+--------+-----------------------------------------------------+
| Dave  |   67   |  This is some text made by Dave                     |  <-- Note the next sentence is on the next line
|       |        |  He is a dentist and works five days a week         |
|       |        |                                                     |  <-- Note there is a blank line between these two sentences 
|       |        |  He enjoys sports and loves burgers                 |
|       |        |  He hates pizza                                     |
+-------+--------+-----------------------------------------------------+
| Paul  |   34   |  This is some text from a different person          |
|       |        |                                                     | 
|       |        |  Paul is always very happy                          |
|       |        |  He loves dogs                                      | 
+-------+--------+-----------------------------------------------------+
| Lucy  |   27   |  Lucy is the only girl in this database table       |
|       |        |  She likes coffee                                   |
|       |        |                                                     |
|       |        |  She does karate at the weekend                     |
+-------+--------+-----------------------------------------------------+

The problem (greatly simplified for the purposes of explaining my question):

I am using the personal_details table to create a View called personal_details_view with the following simple SQL:

SELECT personal_details.name, personal_details.age, peronsal_details.details 
FROM personal_details

However in this View, in the Details field of the table above I want to insert a particular string at the end of each sentence depending on whether there is a single newline or a double new line.

For example in the View the Detail fields for Dave, Paul and Lucy would become:

---------------------------------------------------------
This is some text made by Dave</text></line><line><text>                                               <-- to signify a SINGLE newline followed this
He is a dentist and works five days a week</text></line><line><text></text></line><line><text>         <-- to signify a DOUBLE newline followed this
He enjoys sports and loves burgers</text></line><line><text>
He hates pizza
--------------------------------------------------------

---------------------------------------------------------
This is some text from a different person</text></line><line><text></text></line><line><text>          <-- to signify a DOUBLE newline followed this
Paul is always very happy</text></line><line><text>                                                    <-- to signify a SINGLE newline followed this 
He loves dogs
---------------------------------------------------------

---------------------------------------------------------
Lucy is the only girl in this database table</text></line><line><text>                                 <-- to signify a SINGLE newline followed this 
She likes coffee</text></line><line><text></text></line><line><text>                                   <-- to signify a DOUBLE newline followed this
She does karate at the weekend
---------------------------------------------------------

</text></line><line><text> signifying a single new line followed this sentence

</text></line><line><text></text></line><line><text> signifying a double new line followed this sentence.

I may be overlooking an obvious way this can be done, but I cannot see a logical way of doing this.

Upvotes: 0

Views: 341

Answers (1)

foibs
foibs

Reputation: 3406

In this case, there is no real distinguishing between one newline or two newlines

Does this do the job?

select replace(details, E'\n', '</text></line><line><text>'||E'\n') from personal_details

EDIT: After reading your latest edit with extra care to the desired result, I also suggest a double replace:

select replace(
  replace(details, E'\n\n', '</text></line><line><text>'||E'\n'),
E'\n', '</text></line><line><text>'||E'\n')
from personal_details

The inner replace which runs first, replaces all double newline chars with your desired extra string just once, plus one newline,

while the outer replace further adds the desired string in all newlines encountered.

If you want single line output in your file, you can just remove the last ||E'\n' of the outer replace

Upvotes: 2

Related Questions