SQL replace part of a string

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

Cheers,

Lain