How to replace string in SQLite?

问题:

How to update table column having this:

/var/mobile/233KKFSDK3234/Documents/Page.jpg

and replace it with

/Documents/Page.jpg

in SQLite?

Note: All text, except /Documents/ is dynamic.

回答1:

You have to read first the string and put it in to an nsstring and then generate newstring form the old one and then update in to db.

Here you have the objective c code for generate the new string. The db update you can doit with the other answers.

    NSString *originalString = @"/var/mobile/233KKFSDK3234/Documents/Page.jpg";

    NSRange documentsRage = [originalString rangeOfString:@"/Documents/"];

    NSString *newString = [NSString stringWithString:[originalString substringWithRange:NSMakeRange(documentsRage.location, [originalString length]-documentsRage.location)]];
    NSLog(@"newString:%@",newString);

回答2:

UPDATE table SET column = IF(
    POSITION('/Documents/' IN column),
    RIGHT(column, (LENGTH(column) - POSITION('/Documents/' IN column) - LENGTH('/Documents/') + 1)),
    column
)

If the pattern is found, only keep characters right of the occurrence; else, do nothing (keep old value).

回答3:

Following worked for me:

UPDATE yourtable SET yourfield = SUBSTR(yourfield,18);

..as stated by Bjoern on this question: How to Update this data using SQL?

回答4:

Do the string processing in ObjC and then Use this query.

update tablename set columnname = 'processedString' ;

Updated as per your requirement