AlphaNumeric上的拆分值更改为多行

Question

我有一个存储过程的以下输出，我正在尝试拆分Valueasstring，它在数值和alpha之间切换到它自己的行中，并更新了两个值：

Acct    Valuetypename   Valueasstring   EffectiveDate
123     Accepted        150ABC          8/15/2017
234     Accepted        500DF           10/17/2017
345     Accepted        1000ABC         10/17/2017
456     Accepted        25PV            10/3/2017
567     Accepted        100PV           8/15/2017

我试着用一个设置字符来拆分，就像使用XML的逗号一样，但是在类型更改时却陷入了困境。我不打算使用XML。

Select a.Acct, a.Valuetypename, b.Valueasstring as ValueAsString, a.EffectiveDate
FROM
(
    SELECT *,
    CAST('<X>'+replace(T.Valueasstring,',','</X><X>')+'</X>' as XML) as my_Xml 
    FROM MyTable T
) a
CROSS APPLY
(
    SELECT my_Data.D.value('.','varchar(50)') as Valueasstring
    FROM a.my_Xml.nodes('X') as my_Data(D)
) b

我想要实现的是，多行，valuetypename改为alpha值，Valueasstring改为1(是)。

Acct    Valuetypename   Valueasstring   EffectiveDate
123     ABC             1               8/15/2017
123     Accepted        150             8/15/2017
234     DF              1               10/17/2017
234     Accepted        500             10/17/2017
345     ABC             1               10/17/2017
345     Accepted        1000            10/17/2017
456     PV              1               10/3/2017
456     Accepted        25              10/3/2017
567     PV              1               8/15/2017
567     Accepted        100             8/15/2017

在我的代码中，我只能找到如何在设置字符上分隔逗号。但在我的情况下，我没有设定字符，甚至没有一个空格，只是从阿尔法切换到数字，反之亦然。

Answer 1

首先，您可以用逗号,分隔它们。( (i.e. 150, ABC)通过使用stuff()函数。

select 
       a.Acct,
       case when ISNUMERIC(m.value('.', 'varchar(max)')) <> 1 then m.value('.', 'varchar(max)') else a.Valuetypename end [Valuetypename],
       case when ISNUMERIC(m.value('.', 'varchar(max)')) <> 1 then '1' else m.value('.', 'varchar(max)') end [Valueasstring],
       a.EffectiveDate from
(
    SELECT *, CAST('<m>'+replace(STUFF(Valueasstring,PATINDEX('%[A-Z]%', Valueasstring),0,','),',','</m><m>')+'</m>' as XML) as my_Xml  FROM  MyTable 
)a cross apply my_Xml .nodes('/m') as Valueasstring(m)

并且，借助简单的isnumeric()表达式通过case函数检查数值。

结果：

Acct    Valuetypename   Valueasstring   EffectiveDate
123     ABC             1               8/15/2017
123     Accepted        150             8/15/2017
234     DF              1               10/17/2017
234     Accepted        500             10/17/2017
345     ABC             1               10/17/2017
345     Accepted        1000            10/17/2017
456     PV              1               10/3/2017
456     Accepted        25              10/3/2017
567     PV              1               8/15/2017
567     Accepted        100             8/15/2017

Answer 2

如果没有分隔字符，就不能使用拆分，但可以使用PATINDEX查找模式的第一次出现：

SET DATEFORMAT mdy;
DECLARE @mockupTable TABLE(Acct INT,Valuetypename VARCHAR(100),Valueasstring VARCHAR(100),EffectiveDate DATE);
INSERT INTO @mockupTable VALUES
 (123,'Accepted','150ABC','8/15/2017')
,(234,'Accepted','500DF','10/17/2017')
,(345,'Accepted','1000ABC','10/17/2017')
,(456,'Accepted','25PV','10/3/2017')
,(567,'Accepted','100PV','8/15/2017');

查询使用CROSS APPLY作为命名变量获取职位的值。可以将PATINDEX放在查询中两次，但更好的做法是：

SELECT m.Acct
      ,m.Valuetypename
      ,LEFT(Valueasstring,FirstAlpha.Position-1) AS Numpart
      ,SUBSTRING(Valueasstring,FirstAlpha.Position,4000) AS Restpart
      ,m.EffectiveDate
FROM @mockupTable AS m
CROSS APPLY (SELECT PATINDEX('%[a-zA-Z]%',m.Valueasstring) AS Position) AS FirstAlpha

结果

Acct              Numpart   Restpart    EffectiveDate
123     Accepted    150     ABC         2017-08-15
234     Accepted    500     DF          2017-10-17
345     Accepted    1000    ABC         2017-10-17
456     Accepted    25      PV          2017-10-03
567     Accepted    100     PV          2017-08-15

可以使用简单的CAST (... AS INT)将Numpart转换为数字。