SQL Server Compact Query Analyzer: how to change part of a row value in a table?

Question

I have a SQL Server Compact Edition 4.0 database file with the extension .sdf . The database is created by the book cataloging application. The database contains many tables, including a table called Book , which contains one row for each book in the database. The table has many columns, including a column named FrontCover , which contains a string value that contains 2 parts: path part and file name part the image file for the front cover of the book, for example:

 Documents and Settings\Boson\My Documents\Books\Covers\0596003978.jpg 

In this example, part of the path:

 'Documents and Settings\Boson\My Documents\Books\Covers' 

while part of the file:

 '0596003978.jpg' 

Some books do not contain a value for the FrontCover column because the front cover is not available. For such books, the FrontCover column FrontCover empty. However, if the book has an image file on the front cover, then the string value has the same part of the path, but a different part of the file. For example, for another column of the book, FrontCover has this value:

 'Documents and Settings\Boson\My Documents\Books\Covers\1590596633.jpg' 

As we see, the part of the path is the same as in the first example, namely:

 'Documents and Settings\Boson\My Documents\Books\Covers' 

but part of the file is different:

 '1590596633.jpg' 

PROBLEM

I want to change the entire Book table so that the string values ​​of the FrontCover column are changed for each book in the table so that part of the file remains the same, but part of the path changes:

 'Documents and Settings\Boson\My Documents\Books\Covers' 

to

 'Books\AEM database\Covers' 

The string value of the FrontCover column for the book in the first example will thus change from:

 'Documents and Settings\Boson\My Documents\Books\Covers\0596003978.jpg' 

to

 'Books\AEM database\Covers\0596003978.jpg' 

Part of the file is the same, but part of the path changes. I want to modify the entire Book table so that the portion of the string value file for the FrontCover column remains the same, but the portion of the path is changed as described above.

The book cataloging application that owns the .sdf database .sdf is stupid and cannot do the job. So I installed a simple open source SQL viewer / editor application called SQL Compact Query Analyzer (http://sqlcequery.codeplex.com/). SQL Compact Query Analyzer can open .sdf database .sdf and accept SQL commands to modify a .sdf database .sdf .

Can you help me with SQL commands that can do this job?

Thanks in advance for your help.

Best wishes

Answer 1

 UPDATE Book SET FrontCover = REPLACE( CAST(FrontCover AS NVARCHAR(300)), 'Documents and Settings\Boson\My Documents\Books\Covers', 'Books\AEM database\Covers') WHERE FrontCover like 'Documents and Settings%' 

Note: where clause may not be needed, but ensures that you only replace lines starting with "Documents and Settings ..."

Answer 2

The answer written by Paul Brown is fine, however he issued the following error code:

ErrorCode: -2147467259 [SQL Server Compact ADO.NET Data Provider] HResult: -2147217900, NativeError: 25922 ErrorMessage: The specified argument value for the function is not valid. [Argument # = 1, Function Name (if known) = REPLACE]

The reason for this is because the date type for the FrontCover column is NTEXT (I forgot to specify this in my question), and the REPLACE function does not allow the NTEXT variable. Therefore, before passing it to REPLACE, we must hide the FrontCover variable with the following command:

CAST (FrontCover AS NVARCHAR (100))

So the answer to my question is:

UPDATE SET FrontCover = REPLACE (CAST (FrontCover AS NVARCHAR (100)), 'Documents and Settings \ Boson \ My Documents \ Books \ Covers \', 'Books \ AEM Database \ Covers \')