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


SQL Query to replace text in a specific database field

Posted on 2011-10-19
Medium Priority
Last Modified: 2012-05-12
I am  trying to do a SQL query to replace the period character "." with the hyphen character "-" ONLY in one specific field in a database table.  I am not seeking to replace the character in every single field in the table, only in the 'Field1' field.  I know that SQL contains a REPLACE statement, however this statement replaces every instance of text in the table, which I do not want.
Question by:zintech

Accepted Solution

saimazz earned 1000 total points
ID: 36994799
UPDATE [table]
SET [column] = REPLACE([column], '.', '-')
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 36994801
This query will replace only one column
UPDATE YourTablename
SET YourSingleColumn = REPLACE(YourSingleColumn, '.', '-')

Open in new window

Did I understand your question rightly ?
LVL 33

Assisted Solution

jppinto earned 1000 total points
ID: 36994803
UPDATE TableName
SET FieldName= REPLACE(FieldName, '.', '-' )
WHERE FieldNametoCheck= 'test'
Technology Partners: 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!

LVL 23

Expert Comment

by:Rajkumar Gs
ID: 36994830
As jppinto showed, If you want to replace only some records which is satisfying some criteria, you need add WHERE condition also.


Expert Comment

ID: 36994856
sorry of my fault

Expert Comment

ID: 36994907
I'm not sure I'm catching you right.
Please put table design and sample result sets and desired resultsets.

As far i can understand:
you are looking to modify only one column from a table.

create table:
      create tab1 (field1varchar(25) ,field2 varchar(25));
then insert data:
      insert into tab1 (field1,field2) select 'asasdasd.asd.asd','asdasda.asd.3.as'
select data:
        field1                                                         field2
        ------------------------------------                  ---------------------------------------
        asasdasd.asd.asd                                        asdasda.asd.3.as

now use REPLACE function:
          select replace(field1,'.','-') as field1,field2 from tab1

select data:
          field1                              field2
          ------------------------      -----------------------
          asasdasd-asd-asd      asdasda.asd.3.as

Hence you can update the field1 as well in similar fashion.

Vinod Pottekkatt


Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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
Suggested Courses

834 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