时间戳

首页 > 解决方案 > 如何从列中提取值并更新另一列中的结果

sql - 如何从列中提取值并更新另一列中的结果

问题描述

我有这张桌子：

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')

我想从 FullDate 中提取如下内容：

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... )

并更新在名为 IdDate 的第一列中提取的新值

我的最终结果应该是：

IdDate        FullDate
120140104     Nº1 (26) - Friday 4, January 2014
220150210     Nº2 (64) - Monday 10, February 2015

标签： sqlsql-serversplit

解决方案

如果对辅助表值函数开放：

例子

Declare @YourTable table (IdDate int,FullDate varchar(max))
Insert Into @YourTable values
 (0,'Nº1 (26) - Friday 4, January 2014')
,(0,'Nº2 (64) - Monday 10, February 2015')

Update A
   set IdDate = substring(Pos1,3,10)
              + try_convert(varchar(10),try_convert(date,Pos6+' '+Pos5+' '+Pos7),112)
 From  @YourTable A
 Cross Apply [dbo].[tvf-Str-Parse-Row](FullDate,' ') B

退货

IDDate      FullDate
120140104   Nº1 (26) - Friday 4, January 2014
220150210   Nº2 (64) - Monday 10, February 2015

如果它有助于可视化，则 TVF 返回

在此处输入图像描述

感兴趣的功能

CREATE FUNCTION [dbo].[tvf-Str-Parse-Row] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (
    Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
          ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
          ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
          ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
          ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
          ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
          ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
          ,Pos8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
          ,Pos9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
    From  (Select Cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
)

或者没有这个功能

Update A
   set IdDate = substring(Pos1,3,10)
              + try_convert(varchar(10),try_convert(date,Pos6+' '+Pos5+' '+Pos7),112)
 From  @YourTable A
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                      ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
                      ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
                      ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
                From  (Select Cast('<x>' + replace((Select replace(FullDate,' ','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

编辑

这是 Shawn 的清洁解决方案的扩展版本

Update @YourTable 
   set IdDate = substring(left(FullDate,charindex(' ',FullDate)-1),3,25)
               +try_convert(varchar(10),try_convert(date,replace(substring(FullDate, charindex(',', FullDate) - 2, 100), ',', '')),112)


Select * from @YourTable

推荐阅读