Find and Replace Column values in a Table using T-SQL

If you would like to find some charater/s in a column in the table and replace it here is a simple querry.

Note: For beginner please make sure you know what an update statement does. This query is going to update the column values in your table. So if you are just testing please use the Select statement instead. I have added it to the bottome of this article.

Approach 1:
Update T
       Set T.Col1 = Replace (T.Col1, “@” , ‘at’)
       From MyTable T

But If we just want to make it a bit faster depending on the size of your table.
Approach 2:
Update T
       Set T.Col1 = Replace (T.Col1,”@”,’at’)
       From MyTable T
       Where T.Col1 like (“@”)

You can have a hang of what the Replace functions does from the following link. But I would give you a little idea over here too. The Replace function look for the string that you have provided as the second parameter in the table column or string expression  that you have provided as the first parameter and simply replaces that string with the third parameter.

*Select Replace(T.Col1,”@”, ‘at’) As [ColName]
      From MyTable T

Digg!
 

Author: dotnetolympians

I am an IT Professional and a Microsoft Evangelist working in the field of web applications. I love to program and spend most of my time online in disscusion groups and forums. [rockyou id=63046560]

5 thoughts on “Find and Replace Column values in a Table using T-SQL”

  1. hi

    i have 2 tables in DB. followind is the schema :

    tblPostingAccount(PostingAccountCode,Description,type,OpeningBalance, Balance)
    tblDetails(PostingAccountCode,debit,credit,VoucherNo)

    i want to show all posting account codes from tblPostingAccount and their respective sum of debit and credit columns ..

    like this

    PositngAccountCOde,Debit,Credit,OpeningBalance,Balance

    1-0-1-0-1 7000 6799 699999 9000000
    1-0-1-0-2 7000 6799 699999 9000000
    1-0-1-0-3 7000 6799 699999 9000000
    1-0-1-0-4 7000 6799 699999 9000000
    1-0-1-0-5 7000 6799 699999 9000000
    1-0-1-0-6 Null Null 699999 9000000

    the last code has nothing in tbldetails table ..i want a query from which i could show results as above ..

    Plz help . Thanks in Advance .

    Failure takes u higher than Succes

    Reply

  4. REPLACE(MyField, ‘a’, ‘b’)
    REPLACE(MyField, ‘!’, ‘b’)
    REPLACE(MyField, ‘a’, ‘!’)
    REPLACE(MyField, ‘a’, ‘.’)

    Any reason why all of those work… except the last one?
    Is “.” (period) some special character in SQL or SQLITE?

    Reply

Leave a comment