Simple SQL Server Functions to Solve Real-World Problems

Introduction

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 restoration 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. We will provide SUBSTRING in SQL Server with example.

• The SQL 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 SQL Server 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.

The REPLACE function returns a string where it replaces a substring with another substring.

Take a look at 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 the SQL REPLACE Function in an UPDATE statement

We can achieve the same results using the REPLACE() function in SQL (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

Conclusion

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:

References

1. The LEN() Function
2. The SUBSTRING() Function
3. The REPLACE Function