SQL replace part of a string

Question

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.

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.

lainrobertson · Answer

mrung01&nbsp;&nbsp;Hi, Matt.&nbsp;I'm unclear on whether you're trying to update the original table or if you're just trying to create a view.&nbsp;The latter's easier so I'm going to run with that as the example.&nbsp;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.&nbsp;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.&nbsp;Example T-SQL&nbsp;-- 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&nbsp;&nbsp;&nbsp;Example output&nbsp;&nbsp;Cheers,Lain

mrung01 · Answer

LainRobertson&nbsp;&nbsp;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.

lainrobertson · Answer

mrung01&nbsp;&nbsp;Hi, Matt.&nbsp;Here's an UPDATE variation using the same data structures and data from above.&nbsp;This UPDATE will take this original data:&nbsp;&nbsp;And produce this (where only row 1 has updated):&nbsp;&nbsp;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&nbsp;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).&nbsp;Cheers,Lain

Forum Discussion

SQL replace part of a string

3 Replies

Example T-SQL

Example output

Example UPDATE

Resources