Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to change part of a string value in a table?

Author  Topic 

user182368
Starting Member

5 Posts

Posted - 2012-12-24 : 14:42:53
Hi there,

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

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

In this example path part is:
Documents and Settings\Boson\My Documents\Books\Covers
while file part is:
0596003978.jpg


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

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

As we can see the path part is the same as in the first example, namely:
Documents and Settings\Boson\My Documents\Books\Covers
but the file part is different:
1590596633.jpg


------------------------------------------------------------
PROBLEM:
------------------------------------------------------------

I want to change table "Book" so that string values of the column "FrontCover" are modified in such a way that file part is kept the same but the path part is changed from:

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

Books\AEM database\Covers

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

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

to

Books\AEM database\Covers\0596003978.jpg


File part is the same but the path part is changed.


The book cataloging application which owns the .sdf database file is stupid and cannot do the job. Therefore I have installed a simple open source SQL viewing/editing application called CompactView (http://sourceforge.net/p/compactview/home/Home/) which runs on top of free Microsoft SQL Server Compact Edition 4.0 run-time. CompactView can open .sdf database files and accepts SQL commands in order to modify the .sdf database file.


Can you please help me with the SQL commands which can do the job?

Thank you very much in advance for your help.

best regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-24 : 15:26:13
in t-sql you can use REPLACE for this

something like

UPDATE Books
SET FrontCover=REPLACE(FrontCover,'Documents and Settings\Boson\My Documents\Books\Covers\','Books\AEM database\Covers\')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-12-24 : 15:28:35
One of these maybe? The first works only for your example, where jpgs start with a number, the second


DECLARE @From varchar(1000) = 'Documents and Settings\Boson\My Documents\Books\Covers\0596003978.jpg'
DECLARE @to varchar(100) = 'Books\AEM database\Covers\'
select @From, STUFF(@from,1,PATINDEX('%[0-9]%',@from),@to)
,STUFF(@FROM,1,LEN(@FROM) - PATINDEX('%\%',REVERSE(@FROM))+1,@to)

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

user182368
Starting Member

5 Posts

Posted - 2012-12-25 : 06:32:59
quote:
Originally posted by visakh16

in t-sql you can use REPLACE for this

something like

UPDATE Books
SET FrontCover=REPLACE(FrontCover,'Documents and Settings\Boson\My Documents\Books\Covers\','Books\AEM database\Covers\')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/






Thank you for your answer, however the instructions you proposed caused a software exception to occur in CompactView application which I use to edit .sdf files.

I also tried another open source application which can open .sdf files and execute SQL queries, namely SQL Compact Query Analyzer (http://sqlcequery.codeplex.com/) and it reported the error message:

"The specified argument value for the function is not valid. [ Argument # = 1,Name of function(if known) = REPLACE ]"


Do you know an application which can execute T-SQL queries and edit .sdf (SQL Server Compact Edition database files)?

thank you.
Go to Top of Page

user182368
Starting Member

5 Posts

Posted - 2012-12-25 : 06:41:52
quote:
Originally posted by jimf

One of these maybe? The first works only for your example, where jpgs start with a number, the second


DECLARE @From varchar(1000) = 'Documents and Settings\Boson\My Documents\Books\Covers\0596003978.jpg'
DECLARE @to varchar(100) = 'Books\AEM database\Covers\'
select @From, STUFF(@from,1,PATINDEX('%[0-9]%',@from),@to)
,STUFF(@FROM,1,LEN(@FROM) - PATINDEX('%\%',REVERSE(@FROM))+1,@to)

Jim

Everyday I learn something that somebody else already knew




Thank you for your answer.

Since I do not know SQL could you please clarify me if the code section you proposed updates the whole "Book" table or just one field for one book entry (from your code: 'Documents and Settings\Boson\My Documents\Books\Covers\0596003978.jpg')

I need an SQL query which has to update the whole "Book" table! That is to say in every row (i.e. for each book entry) in the "Book" table I need to replace part of the string value from 'Documents and Settings\Boson\My Documents\Books\Covers' to 'Books\AEM database\Covers'. The ending part of the string (file name part) has to be unchanged.


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-26 : 00:09:06
quote:
Originally posted by user182368

quote:
Originally posted by visakh16

in t-sql you can use REPLACE for this

something like

UPDATE Books
SET FrontCover=REPLACE(FrontCover,'Documents and Settings\Boson\My Documents\Books\Covers\','Books\AEM database\Covers\')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/






Thank you for your answer, however the instructions you proposed caused a software exception to occur in CompactView application which I use to edit .sdf files.

I also tried another open source application which can open .sdf files and execute SQL queries, namely SQL Compact Query Analyzer (http://sqlcequery.codeplex.com/) and it reported the error message:

"The specified argument value for the function is not valid. [ Argument # = 1,Name of function(if known) = REPLACE ]"


Do you know an application which can execute T-SQL queries and edit .sdf (SQL Server Compact Edition database files)?

thank you.


whats data type of FrontCover column?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

user182368
Starting Member

5 Posts

Posted - 2012-12-26 : 04:38:36
quote:
Originally posted by visakh16

quote:
Originally posted by user182368

quote:
Originally posted by visakh16

in t-sql you can use REPLACE for this

something like

UPDATE Books
SET FrontCover=REPLACE(FrontCover,'Documents and Settings\Boson\My Documents\Books\Covers\','Books\AEM database\Covers\')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/






Thank you for your answer, however the instructions you proposed caused a software exception to occur in CompactView application which I use to edit .sdf files.

I also tried another open source application which can open .sdf files and execute SQL queries, namely SQL Compact Query Analyzer (http://sqlcequery.codeplex.com/) and it reported the error message:

"The specified argument value for the function is not valid. [ Argument # = 1,Name of function(if known) = REPLACE ]"


Do you know an application which can execute T-SQL queries and edit .sdf (SQL Server Compact Edition database files)?

thank you.


whats data type of FrontCover column?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





According to CompactView (it generates SQL Schema in a separate tab with details about the Book table) column FrontCover is of type NTEXT

I first thought that the SQL parser in the application I use does not accept REPLACE function (normally it highlights the reserved words it recognizes and it doesn't highlight REPLACE), however I modified the command you proposed and I typed:

UPDATE Book
SET FrontCover = REPLACE ('Covers12345Covers67890','Covers','ABCDEF')

and after executing the query all the rows in the table Book have been changed in the column FrontCover with this string value:

ABCDEF12345ABCDEF67890

This means that the function REPLACE has been recognized and that it does not accept FrontCover as the first argument.
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-26 : 07:36:00
>>column FrontCover is of type NTEXT

REPLACE function won't allow NTEXT variable.. So covert it to

REPLACE (CAST(FrontCover AS NVARCHAR(MAX)) ,'Covers','ABCDEF')



--
Chandu
Go to Top of Page

user182368
Starting Member

5 Posts

Posted - 2012-12-26 : 08:21:44
quote:
Originally posted by bandi

>>column FrontCover is of type NTEXT

REPLACE function won't allow NTEXT variable.. So covert it to

REPLACE (CAST(FrontCover AS NVARCHAR(MAX)) ,'Covers','ABCDEF')



--
Chandu



Thank you very much for your valuable input. That was improvement because I do not receive any more the previous error code, however I had to replace MAX with a number. After typing:

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

The table is successfully updated.

Thank you all very much for your valuable help. Merry Christmas and Happy New Year.
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-26 : 08:40:37
If FrontCover is of below 8000 size data, then replace that MAX with 8000

--
Chandu
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-26 : 23:45:30
quote:
Originally posted by user182368
The table is successfully updated.
Thank you all very much for your valuable help. Merry Christmas and Happy New Year.

Welcome.. wish you the same

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-26 : 23:54:32
quote:
Originally posted by user182368

quote:
Originally posted by bandi

>>column FrontCover is of type NTEXT

REPLACE function won't allow NTEXT variable.. So covert it to

REPLACE (CAST(FrontCover AS NVARCHAR(MAX)) ,'Covers','ABCDEF')



--
Chandu



Thank you very much for your valuable input. That was improvement because I do not receive any more the previous error code, however I had to replace MAX with a number. After typing:

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

The table is successfully updated.

Thank you all very much for your valuable help. Merry Christmas and Happy New Year.


compact wont support blob data (max) so you've to use number

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -