Como extrair valores da coluna e atualizar o resultado em outra coluna
Eu tenho esta tabela:
CREATE TABLE MyTable (
IdDate int,
FullDate varchar(255)
);
insert into MyTable (IdDate,FullDate)
VALUES (0, 'Nº1 (26) - Friday 4, January 2014'),
(0,'Nº2 (64) - Monday 10, February 2015')
Eu quero extrair do FullDate algo como isto:
1 2014 01 04
2 2015 02 10
1st number is extracted from Nº1
2nd number is extracted from Year
3rd number is extracted from Month (convert January to 01)
4th number is extracted from day (if day < 10, add 0 at the beginning: 01,02... )
E atualize o novo valor extraído na primeira coluna chamada IdDate
Meu resultado final deve ser como:
IdDate FullDate
120140104 Nº1 (26) - Friday 4, January 2014
220150210 Nº2 (64) - Monday 10, February 2015