I need letters added to contents of a field in a database if other letters are present.

My table name is "webstockforuploadrob" and the field name is "thumb nail image name". An example of one of the field contents is XX152TH.jpg. I need a query that adds P1 before the TH.jpg if TH.jpg is present. The result should read XX152P1TH.jpg Hope someone can help
oldmillsaddleryAsked:
Who is Participating?
 
DimitrisConnect With a Mentor Senior Solution ArchitectCommented:
This works.

UPDATE TableName
SET Image_name = REPLACE(image_name,'TH.jpg','P1TH.jpg')
Where RIGHT(Image_name,3)='TH.jpg'

Open in new window

0
 
DimitrisSenior Solution ArchitectCommented:
Sorry the where is like thsi
WHERE RIGHT(image_name,5)='TH.jpg' :)
0
 
oldmillsaddleryAuthor Commented:
thanks for this but I am not getting it totally correct. I have a table called newwebstock and one of the fields is called Thumbnail Image name

 UPDATE NEWWEBSTOCK SET Thumbnail_Image_name = REPLACE(Thumbnail_Image_name,'TH.jpg','P1TH.jpg')
WHERE RIGHT(Thumbnail_Image_name,5)='TH.jpg';


when I run the query it asks me to enter parameter value for  Thumbnail_Image_name

Sorry it is something stupid I am doing I guess
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Arthur_WoodConnect With a Mentor Commented:
are you sure that the field is named "Thumbnail_Image_name"
Also, this query will lead to significant problems if it is inadvertantly executed successfully more that once.  After the first succesful execution, the field Thumbnail_Image_name will still end with the final 5 characters as specified in the where clause, and thus a second application of the query will again find all the same records, and will modify the end of the field in the same way, so that the field will then read (for example):
XX152P1P1TH.jpg  
 Initially                      After First Eexcution               After Second Execution
XX152TH.jpg            XX152P1TH.jpg                     XX152P1P1TH.jpg
0
 
Arthur_WoodCommented:
in the original question, you state
the field name is "thumb nail image name".  
in the query you posted, you have the name as  
 Thumbnail_Image_name
which is it, as it cannot be both.  The fact that you are getting a prompt for that 'parameter' name (Thumbnail_Image_name) suggests that the field is actually named  
thumb nail image name
0
 
awking00Commented:
Can you post a describe for newwebstock so we can see actual field name? Also, you can eliminate additional executions by adding -
and RIGHT(Thumbnail_Image_name,7) <>'P1TH.jpg';
0
 
oldmillsaddleryAuthor Commented:
Thanks all I seem to have got things sorted with the file name
0
 
Arthur_WoodCommented:
Glad to be of assistance.  Just need to be sure that once this query has been executed, that it is not executed again, as I noted, above.
 
AW
0
 
awking00Commented:
By the way, it should be
where right(<fieldname>,6) = 'TH.jpg'
and right(<fieldname>,8) <> 'P1TH.jpg'
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.