[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 183
  • Last Modified:

SQL - Select and Replace Syntax

I have a bunch of PDF drawings that are listing in a BRIDGEPHOTO Table in SQL Server 2012 , Problem is some of the pdf names include the "&" symbol. This is a reserved word in ESRI and the attachments are not working.

I would just like to Delete that from the name all together.
So Plan&Profile becomes PlanProfile or Plan_Profile
I can replace with and "_" as well. Either way is good with me.

SELECT REPLACE(AliasName,'&', '')
from [BRIDGEPHOTO]
Where AliasName Like '&'
GO

I'm not sure if this is right and was hoping to double check, Plus get some feedback on the best way to do this.
Thanks
0
PtboGiser
Asked:
PtboGiser
  • 3
  • 3
  • 2
  • +1
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Should be:
SELECT REPLACE(AliasName,'&', '')
 from [BRIDGEPHOTO]
 Where AliasName Like '%&%'

Open in new window


You can also use this update statement:
UPDATE [BRIDGEPHOTO]
SET AliasName = REPLACE(AliasName,'&', '')

Open in new window

0
 
SimonCommented:
I could be off the mark here, but is it the actual PDF files that you have stored in the table, or pointers to their location?

If what you have stored are pointers, doing any replace on the filenames may break the link to the source documents.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Almost.

The third line should read

Where AliasName Like '%&%'

However, if you actually want to replace the original table, then you should use

Update [BRIDGEPHOTO]
Set AliasName = REPLACE(AliasName,'&', '')
Where AliasName Like '%&%'
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
PtboGiserAuthor Commented:
Pointers, I have a second question posted on how to rename the actually pdf file stored in Windows Explorer.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can't have the symbol '&' in files.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You can! ampersand in file.
0
 
PtboGiserAuthor Commented:
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I can see now. Only in Windows. I've tried in the Command Prompt and I couldn't. You need to put the filename in quotes "ABC&D.txt" to it can accept.
0
 
PtboGiserAuthor Commented:
Thanks Crew, Got it. I just don't work enough with these syntax's to know them without some guidance
Cheers
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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