[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Using a replace command in SQL Server Express to remove certain strings in a table

Hi,

Using SQL Server Management Studio and I am trying to replace a certain string with another.

The Table - tbl_customer
The Column - Address

For the sake of testing looking for X and replacing it with Y - Here is my current effort.

update tbl_customer SELECT REPLACE(Address, 'X', 'Y')
FROM         tbl_Customer

This seems to find the instance and displays the Y however, it does not update the table?.....

0
FlyingFortress
Asked:
FlyingFortress
  • 3
  • 3
1 Solution
 
Lee SavidgeCommented:
update tbl_customer set address = REPLACE(Address, 'X', 'Y')
FROM         tbl_Customer
0
 
FlyingFortressAuthor Commented:
Ok thanks for this how would I be able to set it to remove unwanted Quote marks. Again my effort

update tbl_customer set address = REPLACE(Address, '%"', 'Y')
FROM         tbl_Customer

Thanks
0
 
Lee SavidgeCommented:
To remove single quotes:

update tbl_customer set address = REPLACE(Address, '''', '')
FROM         tbl_Customer

To remove double quotes:

update tbl_customer set address = REPLACE(Address, '"', '')
FROM         tbl_Customer

There is a subtle difference. When you put in a single quote into a SQL string you must "double up" so use two single quotes when you mean one. This escapes the character.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Alpesh PatelAssistant ConsultantCommented:
update tbl_customer set Address = REPLACE(Address, 'X', 'Y')
FROM         tbl_Customer
0
 
FlyingFortressAuthor Commented:
So will this work if the field contains "X" ?

The Quotes are also at the end of the text as well as the front I used the wild card % is that not needed in this case?

Cheers

FF
0
 
Lee SavidgeCommented:
Replace will work on the whole string so you don't need the %. If the field contains

"X" rather than just X then

update tbl_customer set Address = REPLACE(Address, '"X"', 'Y')
FROM         tbl_Customer

To help more I'd need some examples of the data that you're trying to fix.
0
 
FlyingFortressAuthor Commented:
Thank you!
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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