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
PtboGiserAsked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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
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.