DECLARE @environment VARCHAR(250)

--SELECT @environment = '%.localhost.local'
--SELECT @environment = 'testing.%'
SELECT @environment = 'acceptance.%'
--SELECT @environment = '%.hiddencmsurl.com'


DECLARE @getid CURSOR
DECLARE @newUrl VARCHAR(250)
DECLARE @id INT

SET @getid = CURSOR
FOR SELECT Substring([SysTable].[string03], 0, Charindex('/',
           [SysTable].[string03]))
           + '//' + [Table].[string02] + '/',
           [Reference].[pkid]
    FROM   [dbo].[tblbigtable] AS [Table]
           JOIN [dbo].[tblbigtablereference] AS [Reference]
             ON ( [Table].[pkid] = [Reference].[refidvalue] )
           JOIN [dbo].[tblsystembigtable] AS [SysTable]
             ON ( [Reference].[pkid] = [SysTable].[pkid] )
    WHERE  [Table].[storename] = 'EPiServer.Web.HostDefinition'
           AND [Table].[string02] LIKE @environment

OPEN @getid

FETCH next FROM @getid INTO @newUrl, @id

WHILE @@FETCH_STATUS = 0
  BEGIN
      UPDATE [dbo].[tblsystembigtable]
      SET    [string03] = @newUrl
      WHERE  [pkid] = @id

      FETCH next FROM @getid INTO @newUrl, @id
  END

CLOSE @getid

DEALLOCATE @getid

SELECT [string01] AS [SiteName],
       [string03] AS [Url]
FROM   [dbo].[tblsystembigtable]
WHERE  [storename] = 'EPiServer.Web.SiteDefinition'