Solved

SQL - Select and Replace Syntax

Posted on 2015-02-12
9
144 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 47

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:Simon
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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
 
LVL 47

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 47

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Improvement  ( Speed) 14 28
insert wont work in SQL 14 22
Better way to make a query with date filter. 5 27
T-SQL Default value in Select? 5 27
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

803 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