SQL replace part of a string

Copper Contributor

I am trying to replace part of a string in the tagpath column shown below. I need to replace edge nodes with cloud anywhere that the rest of the string path is equal. There are thousands of records like this. There are some records that will have edge nodes but not a cloud record as well and those need to stay as edge nodes so I cannot do a blanket replace of edge nodes with cloud. Only where there is a record for edge nodes/string = cloud/string

 

In the example below everything is the same after edge nodes or cloud and need to replace edge nodes with cloud wherever the rest of the string is the same.

mrung01_0-1706309027359.png

 

In the example below again everything after edge nodes or cloud is the same, but where it is redlined is different than the example above.

mrung01_2-1706309309950.png

 

 

3 Replies

@mrung01 

 

Hi, Matt.

 

I'm unclear on whether you're trying to update the original table or if you're just trying to create a view.

 

The latter's easier so I'm going to run with that as the example.

 

From your description, it seems as though the "cloud/*" records are your reference point, so the example below grabs those first before finding "matching" "edge nodes/" records.

 

I'm calculating a hash on the cloud records in preference to running calculated string comparison to potentially speed things up. If it's only a few to a few tens of thousands of records, this isn't going to make much/any difference. But it would in much larger volumes.

 

Example T-SQL

 

-- Create the example table.
CREATE TABLE [dbo].[table1]
(
	[id] [int] NOT NULL
	, [tagpath] [varchar](128) NOT NULL
);
GO

-- Add some example data.
INSERT INTO
	[dbo].[table1]
VALUES
	(1, 'edge nodes/fubar/relativehumidity1')
	, (2, 'edge nodes/fubar2/relativehumidity1')
	, (3, 'cloud/fubar/relativehumidity1');
GO

-- Run an example query.
SELECT
	[source].[id]
	, CASE
		WHEN [reference].[checksum] IS NOT NULL THEN
			CONCAT('cloud', SUBSTRING([source].[tagpath], 11, 128))
		ELSE
			[source].[tagpath]
	END AS [tagpath]
FROM
	[dbo].[table1] AS [source] LEFT OUTER JOIN
	(
		SELECT
			DISTINCT
			BINARY_CHECKSUM(SUBSTRING([tp].[tagpath], 6, 128)) AS [checksum]
		FROM
			[dbo].[table1] AS [tp]
		WHERE
			[tp].[tagpath] LIKE 'cloud/%'
	) AS [reference] ON
		BINARY_CHECKSUM(SUBSTRING([source].[tagpath], 11, 128)) = [reference].[checksum]
WHERE
	[source].[tagpath] LIKE 'edge nodes/%';
GO

 

 

 

Example output

LainRobertson_0-1706319899960.png

 

 

Cheers,

Lain

@LainRobertson 

 

Thank you, sorry I didn't specify. Yes I need to update the existing table. I was hoping there would be an "easy" way to do it. Appreciate the reply.

@mrung01 

 

Hi, Matt.

 

Here's an UPDATE variation using the same data structures and data from above.

 

This UPDATE will take this original data:

 

LainRobertson_0-1706405290527.png

 

And produce this (where only row 1 has updated):

 

LainRobertson_1-1706405359332.png

 

Example UPDATE

-- Run an example update.
UPDATE
	[dbo].[table1]
SET
	[tagpath] = CONCAT('cloud', SUBSTRING([source].[tagpath], 11, 128))
FROM
	[dbo].[table1] AS [source] INNER JOIN
	(
		SELECT
			DISTINCT
			BINARY_CHECKSUM(SUBSTRING([tp].[tagpath], 6, 128)) AS [checksum]
		FROM
			[dbo].[table1] AS [tp]
		WHERE
			[tp].[tagpath] LIKE 'cloud/%'
	) AS [reference] ON
		BINARY_CHECKSUM(SUBSTRING([source].[tagpath], 11, 128)) = [reference].[checksum]
WHERE
	[source].[tagpath] LIKE 'edge nodes/%';
GO

 

This approach means you end up with duplicate "cloud" entries. If you don't want that either, then you're looking at a DELETE statement, not an UPDATE (which introduces the additional question of which of the duplicates to keep versus delete).

 

Cheers,

Lain