0

Please login or register to vote for this query.

(click on this box to dismiss)

https://meta.stackexchange.com/questions/305185

Preguntas y respuestas sobre el sitio para programadores y profesionales de la informática

-- set up sites table
CREATE TABLE [#Sites]
(
	Id int NOT NULL primary key, 
	TinyName nvarchar(12) not null,
	Name nvarchar(64) not null, 
	LongName nvarchar(64) not null,
	Url nvarchar(max) not null
)

-- seed with data
insert into #Sites(
Id, 
TinyName,Name,LongName,
Url) values
(1, 
'so', 'StackOverflow', 'Stack Overflow',
'https://stackoverflow.com')

insert into #Sites(
Id, 
TinyName,Name,LongName,
Url) values
(2, 
'mso', 'StackOverflow', 'Stack Overflow',
'https://meta.stackoverflow.com')

insert into #Sites(
Id, 
TinyName,Name,LongName,
Url) values
(3, 
'esso', 'es StackOverflow', 'es Stack Overflow',
'https://meta.es.stackoverflow.com')

insert into #Sites(
Id, 
TinyName,Name,LongName,
Url) values
(4, 
'ptso', 'pt StackOverflow', 'pt Stack Overflow',
'https://meta.pt.stackoverflow.com')

insert into #Sites(
Id, 
TinyName,Name,LongName,
Url) values
(5, 
'me3d', 'meta webapps', 'meta webapps',
'https://meta.webapps.stackexchange.com')
 -- end seed
 
-- test rewrite url query
select url
, charindex('meta.', url) + 5 as [meta. position]
, charindex('.', url,charindex('meta.', url) + 5)  [dot position after  meta.]
, substring(url
          , charindex('meta.', url) + 5
          , charindex('.', url,charindex('meta.', url) +5) - (charindex('meta.', url) +5)) [part between meta and tld]
, substring(url, charindex('.', url,charindex('meta.', url) +5), len(url)) [remainder of tld]
, concat('https://'
  ,substring(url
          , charindex('meta.', url) + 5
          , charindex('.', url,charindex('meta.', url) +5) - (charindex('meta.', url) +5))
   , '.meta'
   , substring(url, charindex('.', url,charindex('meta.', url) +5), len(url))
   ) as [full new url with switched meta]
from #sites
where url like 'https://meta.%.stackoverflow.com'
or url like 'https://meta.%.stackexchange.com'


-- the actual query that will update all rows for meta sites
update #sites
set url = concat('https://'
  ,substring(url
          , charindex('meta.', url) + 5
          , charindex('.', url,charindex('meta.', url) + 5) - (charindex('meta.', url) +5))
   , '.meta'
   , substring(url, charindex('.', url,charindex('meta.', url) +5), len(url))
   )
where url like 'https://meta.%.stackoverflow.com' -- only do those sites
or url like 'https://meta.%.stackexchange.com' -- and those sites


-- see if we got it right 
select *
from #sites

fork query

edited jan 24 18

created dec 28 17 rene