0

I'm in a bit over my head here.

I have an SQL database, and I'm trying to replace all linefeeds (LF), which are NOT preceeded by a whitespace, with a whitespace + the linefeed. I'm using SQLiteStudio for this. What I have right now is the following:

UPDATE table 
SET column = replace( column, '%' + char(10) + '%', ' ' )

When I run the above query, the following data:

<br><strong><font color="2018283286c3">
Lorem ipsum dolor sit amet, consectetur adipiscing[LF]
elit, sed do eiusmod tempor incididunt ut labore et[LF]
<hr size="1px" noshade style="clear:both;margin-top:10px;height:1px;">

... Becomes:

<br><strong><font color="2% %18283286c3">
Lorem ipsum dolor sit amet, consectetur adipiscing[LF]
elit, sed do eiusmod tempor incididunt ut labore et[LF]
<hr size="1px" noshade style="clear:both;margin-top:1% %px;height:1px;">

I have added the [LF]'s in the above for clarity. As can be seen, my query only replaces the zeroes, for some reason, and doesn't match the linefeeds.

What I need to end up with is this:

<br><strong><font color="2018283286c3">
Lorem ipsum dolor sit amet, consectetur adipiscing[WHITESPACE][LF]
elit, sed do eiusmod tempor incididunt ut labore et[WHITESPACE][LF]
<hr size="1px" noshade style="clear:both;margin-top:1% %px;height:1px;">

... so that only LF's NOT already preeceded by a whitespace are matched and replaced with a whitespace + LF. LF's already preeceded by a whitespace are left alone, ideally.

Any ideas what I'm doing wrong, or if there is a better method for this? I found the above query online and have tried to tweak it. Not used to working with these things. Thanks for reading!

Chris W.
  • 1
  • 1

1 Answers1

0

Not sure if your DB setup supports regular expressions, but if so, you can try to do your search/replace with them. Take a look at this link:

replace a part of a string with REGEXP in sqlite3

Once you get your regexp replace function in place, you can use this as your search pattern:

(?P<mystring>.*\S+)\n$

This will match strings that end with a LF, but no whitespace directly preceding it. You can then use the named group "mystring" to construct the string you want.

You can test/revise your regexp here: https://regex101.com/

ravioli
  • 3,749
  • 3
  • 14
  • 28