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 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.

CodingSight - Solving Real-World Problems with Simple 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.

String Data Stored in the Address Column
Figure 1: Result of Listing 1

Invoking the Solution Building Blocks

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
Visualizing the Result of Replacing
Figure 2: Result of Listing 2

Note: We generated a computed column. Thus, we can compare the original values with the changes before we apply those changes.

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

Executing the Change

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.

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

Conclusion

Thus, we have demonstrated the creative use of simple SQL Server functions in a real-life scenario. 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:

Final Contents of the NODE_SAPS Table
Figure 3: Final Contents of the NODE_SAPS Table

References

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

Kenneth Igiri

Kenneth Igiri

Kenneth Igiri is an Enterprise Architect with eProcess International S.A., Ecobank Group's Shared Services Centre. Kenneth has over fifteen years' experience in Information Technology with nine of those years focused on SQL Server and Oracle databases. His interests include Database Performance, HADR, Cloud Computing, Data Architecture and Enterprise Architecture. Asides from work, Kenneth teaches at Children's Church, writes faith-based fiction and helps small businesses grow. You can connect with Kenneth via his blog https://kennethigiri.com, LinkedIn, or on Amazon.com.