Modify Database

[DriverJC]

I'm trying to get a handle on my DVDPedia Database. I have imported my iTunes Library File into DVDPedia and now I need to fix the entries.

For example My iTunes Library file lists "Batman Returns" as "Batman #02: Batman returns".

Is there a way for me to use SQLLIte to search the database looking for all entries with a "#" in them and take Everything AFTER the "#" in zTitle and put it in zCustom2?

Thank you for a great program
Joel

[Conor]

That one is a complicated one as SQLite does not have the capability to cut of a string from a particular character – at least not that I am aware of. There a similar topic here but the poster had the advantage of knowing what the string he wanted to remove was.

Here are the core functions for SQLite that are included in Sqlite. I can't think of any combination that would work.

The function are there to trim substr() or a combination of ltrim(length(zCustom2) - [char location]), the complicated bit is getting the character location. You would have to register your own custom extension function that would be able to do that. Not sure what the mechanism or language is for that in SQLite.

[DriverJC]

For anyone that is interested in how I accomplished this here it is.

For my Movies
All movies in iTunes are named in this format. <Series Title> <Series Index>: <Series Subtitle> (for example: Star Trek series of Movies is labeled like this, Star Trek #01: Star Trek The Motion Picture, Star Trek #02: The Wrath of Kahn, Star Trek #03: The Search for Spock, etc...). I am also putting the <Series> in the field TV Series field for added sorting.

in the Database the Title of the Movie is Star Trek and I use Custom2 (renamed to Album Name) to house #01: Star Trek the Motion Picture. This will allow me to sort the movies by Series so they all come up in order.

However after Importing my XML file from iTunes DVDPEdia put Star Trek #01: The Motion Picture in the Title field. It also put the TV Series field in Series. This allowed me to run the following update in SQLite to cut the title field apart.

Code: Select all

update zEntry set zCustom2 = ltrim(zTitle,zSeries) ;

This update took what was in zTitle, and Removed what was in zSeries from it and placed it in zCustom2.

Worked like a charm.

Thanks for such a great program.
Joel