Solved

SQL - Select and Replace Syntax

Posted on 2015-02-12
9
135 Views
Last Modified: 2015-02-12
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
Comment
Question by:PtboGiser
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40605686
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
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40605694
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40605701
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
 

Author Comment

by:PtboGiser
ID: 40605702
Pointers, I have a second question posted on how to rename the actually pdf file stored in Windows Explorer.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40605709
You can't have the symbol '&' in files.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40605720
You can! ampersand in file.
0
 

Author Comment

by:PtboGiser
ID: 40605737
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40605742
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
 

Author Closing Comment

by:PtboGiser
ID: 40605803
Thanks Crew, Got it. I just don't work enough with these syntax's to know them without some guidance
Cheers
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now