CODEWITHSUNDEEP
stringsqlitereplacepartial
b0red
b0red

Reputation: 47

Trying to change partial value in a field in sqlite databse

I need to update parts of a value in a rather large sickbeard.db. A line in the db looks lite this:

"66"    "305288"    "1" "Stranger Things"   "/media/tv1/Stranger Thins" "Netflix"   "|Adventure|Drama|Fantasy|Horror|Mystery|"  "Scripted"  "50"    "3" "Friday 3:00 AM"    "Continuing"    "0" "0" "2016"  "0" "en"    "0"     "tt4574334" "737297"    "0"     ""  ""  "0" "0" "0" "3"

In the location field the data I need to change is "/media/tv1/Some tv show" but I need to change it to "/media/TV/tv1/Some tv show". This field contains either /media/tv1 or /media/tv2, and I want to add TV so the fields looks like this: /media/TV/tv1

I've been trying this: UPDATE tv_shows SET location = replace( '/media/tv%', '%tv%', '%TV/tv%' ); but it only changes to /media/tv% (it deletes everything else after that.) Which of course breaks the application.

I really dont feel like doing it by hand in the gui

Upvotes: 0

Views: 241

Answers (2)

C Perkins
C Perkins

Reputation: 3886

First of all, according to online documentation, the parameters are like

replace(original, find, replacement)

Replace does not accept patterns. Notice that the the original string must be passed explicitly to the function. (There is no automatic context in which it gets the original string.) Secondly, if you want to ensure the original string matches a particular pattern, for example that you don't make the replacement in some other possible match, you need a WHERE clause to limit the selection. Just like the original string parameter, there is no automatic condition applied based on the parameters... you must use the WHERE clause to properly limit the update:

UPDATE tv_shows 
SET location = replace(location, '/media/tv', '/media/TV/tv')
WHERE location LIKE '/media/tv%'

Note: Since a failed match for replace() returns the original string there may be no apparent negative side effects of just executing this for every row, especially if you expect that the only matches will occur at the beginning of the path string. However, it is always best to include a proper WHERE clause to make the command more efficient (only updating what is necessary) and limiting the negative effect of buggy code (no need to corrupt every row in the table if you get the expression wrong, at least it would only be limited to a subset).

Upvotes: 1

Mateusz Herych
Mateusz Herych

Reputation: 1605

UPDATE tv_shows SET location = replace(location, '/media/', '/media/TV/');

Assuming the column that you want to change is named location.

Upvotes: 1

Related Questions