0

I have a mySQL column called description and I have several sentences in this column. For example: "John ran down a hill. He was tired. John went to get water." I would like a line break after each sentence so that it outputs like:

John went to get water.
He was tired.
John went to get water.

I'm using a SQLite DB Browser (http://sqlitebrowser.org/). I thought I could do line breaks with: "John ran down a hill. \n" but unfortunately it outputs the \n as well as the "". Can anyone help me with these line breaks? Thanks!

chronotrigga
  • 589
  • 1
  • 10
  • 33

1 Answers1

0

SQLite doesn't have string escaping logics. You have to concatenate the newline char using another way, like the char(10) function or typing it in exadecimal: x'0a'. Remember that the character 10 is the newLine for windows architectures.

Just use a query like this (to update all the rows):

update tabel_name set "description" = 
replace("description", '.', '.' || x'0a')

If you want to edit a specific row add a WHERE clause:

update tabel_name set "description" = 
replace("description", '.', '.' || x'0a')
WHERE "rowid" = 1

The || is the concatenation operator in SQLite.

Tobia Zambon
  • 7,479
  • 3
  • 37
  • 69
  • Will this work for only one row? I am a bit confused as to how to use char. Is this a field type? I am currently using text (I was using varchar but line breaks didn't work, text didn't work either) – chronotrigga Apr 10 '15 at 14:02
  • this query will update all of your rows (you have only to change the table name). If you want it to work on specific rows you should add a WHERE clause – Tobia Zambon Apr 10 '15 at 14:03
  • hm.. so assuming I have a rowid column with id numbers I would do: `update table_name set "description" where rowid="1" = replace("description ", '.', '.' || x'0a')` I am a bit new so apologies. – chronotrigga Apr 10 '15 at 14:06
  • Thank you. My last question: so I would put my sentences in the `replace ("John ran down the hill. He needs water.", '.', '.' || x'0a')` correct? – chronotrigga Apr 10 '15 at 14:09
  • no, double quotes are used to specify field names, you should use `replace ('John ran down the hill. He needs water.', '.', '.' || x'0a')`. But I though that the value was already inside the field – Tobia Zambon Apr 10 '15 at 14:12
  • Yes. I was just going to add more. Hm.. for some reason I executed the SQL code but unfortunately it is still the same. Do you have any other tips? My executed code is: update `Main Database` set "description" = replace("description", '.', '.' || x'0a') WHERE "rowid" = 1 – chronotrigga Apr 10 '15 at 14:29
  • If Main database is the table name you should wrap it around double quotes: "Main database" – Tobia Zambon Apr 10 '15 at 14:39
  • Unfortunately I did not solve this issue yet. The concatenation clause did not work in SQLite Manager unfortunately so I am unsure what has happened. I may end up going another route to mitigate this. – chronotrigga Apr 14 '15 at 20:29