SQL - Data modification - Part 8 - Performing Global search and replace in the column of a Table in SQL Server
|
Haziran 4, 2012 by sql tutorial
|
|
Article Information
This SQL Tutorial contains and tries to cover following subjects:
- Explanation of Global search and replace in SQL
- Example to perform global search and replace in SQL
Articles tries to provide answer to following questions
- How to perform global search and replace in SQL server?
- How to replace particular values in a column of table in SQL?
- How to search and replace a string in a column in SQL server?
- How to use Replace() function in SQL server?
Article covers followings indirectly:
- sql update statement
- sql update syntax
Articles pre-requisites following information:
- General knowledge of SQL Server Management Studio
- General knowledge of sql update statement
Updating a Table
In SQL, to update a table, UPDATE command is used. SQL UPDATE command has a basic form, as well as advanced forms to update a table according to different requirements. In this article, we will cover how to use sql update to perform a global search and replace in a column of table within SQL Server, and will look into it in a sql example.
Before performing global search syntax, lets look into basic form of sql update statement again to recall. UPDATE command syntax is as follows:
update table1 set column1 = value
UPDATE customers SET customerName = 'Microsoft'
As above sql update syntax indicates, table name is added after update command. After the table name, we provide the SET and the column name. That was the basic form of SQL update command which we covered in earlier article. If we recall, that sql update command, in its basic form, updates entire column with the given value. If we use above command without a WHERE clause, all rows and customer names will be Microsoft.
Performing Global Search and Replace in a Column of Table
Lets assume that we need to update entire column with replacing a particular string or char with another char or string . SQL UPDATE command serves in that case as a global search and replace when it is combined with REPLACE() function.
Lets see the syntax for updating a column while performing global search and replace.
To apply a global search replace in sql update statement, syntax is as follows:
update table1 set column1 = REPLACE(column1, source string , target string )
In above sql update statement, sql server will look for the all rows in the column and apply a global replace according to given parameters.
Example to global search and replace in a column
To demonstrate how to use replace() function in sql with sql update command, we will perform a update in a column while replacing particular letters with another letter.
As above sql update example indicates, rows which containing particular letter have been updated according to the provided parameter. SQL update command performed a global replace in entire column.
This is an useful method to replace tabs, empty spaces or similar char acters in a column in sql server.
Data Layers
| Area: | programming \ languages \ tsql \ \ \ |
| Ref: | |
| Loc: | articles |
| Tags: | tsql |
|