• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • Last Modified:

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
0
oldmillsaddlery
Asked:
oldmillsaddlery
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
DimitrisSenior 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
Arthur_WoodCommented:
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now