SQL replace part of a string

mrung01 

Hi, Matt.

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

This UPDATE will take this original data:

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

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