如何从列中提取值并更新另一列中的结果 [英] How to extract values from column and update result in another column

问题描述

我有这张桌子:

CREATE TABLE MyTable (IdDate 整数，FullDate varchar(255));插入到 MyTable (IdDate,FullDate)VALUES (0, 'Nº1 (26) - Friday 4, January 2014'),(0,'Nº2 (64) - 2015 年 2 月 10 日星期一')

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

1 2014 01 042 2015 02 10第一个数字是从 Nº1 中提取的第二个数字是从年份中提取的从月份中提取第三个数字(将一月转换为 01)从天中提取第 4 个数字(如果天 <10，则在开头添加 0: 01,02... )

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

我的最终结果应该是:

IdDate FullDate120140104 Nº1 (26) - 2014 年 1 月 4 日，星期五220150210 Nº2 (64) - 2015 年 2 月 10 日星期一

解决方案

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

示例

声明@YourTable 表 (IdDate int,FullDate varchar(max))插入@YourTable 值(0,'Nº1 (26) - 2014 年 1 月 4 日星期五'),(0,'Nº2 (64) - 2015 年 2 月 10 日星期一')更新 A设置 IdDate = 子字符串(Pos1,3,10)+ try_convert(varchar(10),try_convert(date,Pos6+''+Pos5+''+Pos7),112)来自@YourTable A交叉应用 [dbo].[tvf-Str-Parse-Row](FullDate,' ') B

退货

IDDate FullDate120140104 Nº1 (26) - 2014 年 1 月 4 日，星期五220150210 Nº2 (64) - 2015 年 2 月 10 日星期一

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

感兴趣的功能

CREATE FUNCTION [dbo].[tvf-Str-Parse-Row] (@String varchar(max),@Delimiter varchar(10))退货表作为返回 (选择 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)

或者没有函数

更新A设置 IdDate = 子字符串(Pos1,3,10)+ try_convert(varchar(10),try_convert(date,Pos6+''+Pos5+''+Pos7),112)来自@YourTable A交叉申请 (选择 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) 乙

<块引用>

编辑

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

更新@YourTableset IdDate = substring(left(FullDate,charindex('',FullDate)-1),3,25)+try_convert(varchar(10),try_convert(date,replace(substring(FullDate, charindex(',', FullDate) - 2, 100), ',', '')),112)从@YourTable 中选择 *

i have this table:

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

I wanna extract from FullDate something like this:

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

And update the new value extracted in first column called IdDate

My final result should be like:

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

解决方案

If open to a helper Table-Valued Function:

Example

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

Returns

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

If it Helps with the Visualization, the TVF Returns

The Function if Interested

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 
)

Or Without the Function

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

EDIT

This is an expanded version of Shawn's cleaner solution

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

这篇关于如何从列中提取值并更新另一列中的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！