Today’s article comes from a scenario we experienced in practice some time ago. We managed a card transaction system referred to as Postilion, and we had to update the data in a column containing IP addresses as part of the string with a new IP address. This was necessary because the IP address of the server hosting the solution would typically change due to switchover or the data restore to a UAT environment.
The data about the server was stored in the databases, and there was no way to update the same from the application without going row by row. Thus, we had to come up with an efficient solution using the LEN and SUBSTRING (or REPLACE) SQL Server functions.
Reproducing the Environment
For this demonstration, we reproduce only the table required to show what we did. Of course, the data is not the same as in production.
Listing 1 demonstrates how we create and populate our example table:
-- Listing 1: Create and Populate Node_Saps Table create database postilion; use postilion go -- drop table node_saps create table node_saps ( [node] varchar(50) ,[sap] varchar(50) ,[type] varchar(50) ,[protocol] varchar(50) ,[address] varchar(50) ,[setup_data] varchar(50) ,[secure] varchar(50) ,[max_nr_conn] varchar(50) ,[msg_mode] varchar(50) ,[nr_active_conns] varchar(50) ,[filter_properties] varchar(50) ) use postilion go insert into node_saps values ('EGH01',2,'sink','TCP','10.2.100.42_atm_ghana', 100,'YES',10,'open',5,'intense'); insert into node_saps values ('EGH02',3,'sink','TCP','10.2.100.42_atm_ghana', 120,'YES',10,'open',5,'moderate'); insert into node_saps values ('ENG01',4,'source','TCP','10.2.100.42_atm_nigeria', 175,'YES',40,'open',19,'premium'); insert into node_saps values ('EBF01',6,'sink','TCP','10.2.100.42_atm_burkina', 122,'YES',20,'open',4,'intense'); insert into node_saps values ('EGQ01',7,'sink','TCP','10.2.100.42_atm_equatorial', 200,'YES',10,'open',2,'moderate');
This table contains a few simple columns. The column of interest here is the address column. Our task is to change the IP address from 10.2.100.42 to 10.2.100.79.
As shown in Figure 1, the data stored in the address column is not the IP address alone. It is a string, and the IP address is only a part of it. Therefore, we cannot do a simple update. Each row has a different value, and we must isolate the last octet of the IP address and make the change there.
Replace Part of a String in SQL Server Using SUBSTRING() Function
To achieve the task requirements, we use two simple functions: the LEN() function and the SUBSTRING() function.
- The LEN() function returns the number of characters in a string. It is important for our solution because the original data was somewhat dirty – not everything in the address column was an IP address. Thus, we must ensure updating what we intended to update.
- The SUBSTRING() function returns a part of a character, binary, text, or image expression in SQL Server. We use it to ensure that we’ll change that portion of the string in the address column that we want to change – the actual IP Address.
Listing 2 and Figure 2 show the code for visualizing the result of replacing .42 with .79 in the address column.
-- Listing 2: Select Statement to Verify Solution USE postilion GO SELECT [node] ,[sap] ,[type] ,[protocol] ,[address] ,substring (address,1,9) + '79' + substring (address,12,20) manrep ,[setup_data] ,[secure] ,[max_nr_conn] ,[msg_mode] ,[nr_active_conns] ,[filter_properties] FROM [postilion].[dbo].[node_saps] WHERE len(address) > 10
Note: We generated a computed column. Thus, we can compare the original values with the changes before we apply those changes.
Update Part of a String in SQL Using the REPLACE() Function
We could have done this simpler by using the REPLACE() function. It replaces all occurrences of a specified string value with another string value.
Take a look the code in Listing 3. We get the exact same result as shown in Figure 2.
-- Listing 3: Select Statement to Verify Solution Using REPLACE() USE postilion GO SELECT [node] ,[sap] ,[type] ,[protocol] ,[address] ,replace(address,'.42','.79') rep ,[setup_data] ,[secure] ,[max_nr_conn] ,[msg_mode] ,[nr_active_conns] ,[filter_properties] FROM [postilion].[dbo].[node_saps] WHERE len(address) > 10
Syntax for SUBSTRING Function in an UPDATE Statement
Let’s visualize our solution using the SELECT statement. We need to see where we have incorporated the LEN() function along with the SUBSTRING() function or the easier REPLACE() function.
Listing 4 demonstrates how we execute the UPDATE statement. For safety, we enclose it in a transaction. If anything is wrong, we’ll be able to roll it back.
-- Listing 4: UPDATE Statement Using SUBSTRING() BEGIN TRAN update [postilion].[dbo].[node_saps] set address=substring (address,1,9) + '79' + substring (address,12,20) where substring (address,10,2)= '42' and len(address) > 10 SELECT * FROM [postilion].[dbo].[node_saps] --ROLLBACK --COMMIT
If we are satisfied with the results, we simply commit.
Using REPLACE in an UPDATE statement
We can achieve the same results using the REPLACE() function (see Listing 5). This works because of our specific data, “.42” occurs ONLY ONCE in each row.
-- Listing 5: UPDATE Statement Using REPLACE() BEGIN TRAN update [postilion].[dbo].[node_saps] set address=replace(address,'.42','.79') where substring (address,10,2)= '42' and len(address) > 10 SELECT * FROM [postilion].[dbo].[node_saps] --ROLLBACK --COMMIT
Thus, we have demonstrated how to replace a part of some strings in the column using SUBSTRING and REPLACE functions. The success of the task depends on the proper understanding of the data involved. Of course, we need to collaborate with the developers and specialists having the necessary experience to predict the implications of any errors in an application.
In addition, we took precautions:
- We used the SELECT statement and a computed column to determine how the eventual results would look.
- We enclosed our UPDATE statement in a transaction to ensure the rollback option.
You might go further in taking precautionary measures and make a backup of the database: