SQL Query to replace text in a specific database field

Posted on 2011-10-19
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
    LVL 3

    Accepted Solution

    UPDATE [table]
    SET [column] = REPLACE([column], '.', '-')
    LVL 23

    Expert Comment

    by:Rajkumar Gs
    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

    UPDATE TableName
    SET FieldName= REPLACE(FieldName, '.', '-' )
    WHERE FieldNametoCheck= 'test'
    LVL 23

    Expert Comment

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

    LVL 3

    Expert Comment

    sorry of my fault
    LVL 3

    Expert Comment

    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',''
    select data:
            field1                                                         field2
            ------------------------------------                  ---------------------------------------

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

    select data:
              field1                              field2
              ------------------------      -----------------------

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

    Vinod Pottekkatt


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    759 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

    10 Experts available now in Live!

    Get 1:1 Help Now