SQLSERVER使用XML和OUTER APPLY对固定分隔符的字符串截取

分享下用XML和OUTER APPLY对固定分隔符的字符串截取

举例：

 SELECT 1 AS ID,'A@BC@DEF@GHIJ@KLMNO@PQRSTU@VWXYZ' AS COL
 UNION ALL
 SELECT 2 AS ID,'1@23@456@78910@111213@14' AS COL 

截取完效果如下：

1）首先将COL字段转换为XML格式，如下：

SELECT 
  ID
  ,COL
  ,CONVERT(XML, '<root><v>' + REPLACE(CONVERT(NVARCHAR(MAX), COL),'@','</v><v>') + '</v></root>') AS COL_NEW
FROM 
(SELECT 1 AS ID,'A@BC@DEF@GHIJ@KLMNO@PQRSTU@VWXYZ' AS COL
 UNION ALL
 SELECT 2 AS ID,'1@23@456@78910@111213@14' AS COL 
  ) A

2）用XML的notes()和value()方法，对COL_NEW取值，然后用OUTER APPLY跟源表进行拼接，如下：

SELECT 
   T.ID
  ,T1.COL_NEW
  ,T.COL
  ,T.COL_NEW
 
FROM
(
SELECT 
  ID
  ,COL
  ,CONVERT(XML, '<root><v>' + REPLACE(CONVERT(NVARCHAR(MAX), COL),'@','</v><v>') + '</v></root>') AS COL_NEW
FROM 
(SELECT 1 AS ID,'A@BC@DEF@GHIJ@KLMNO@PQRSTU@VWXYZ' AS COL
 UNION ALL
 SELECT 2 AS ID,'1@23@456@78910@111213@14' AS COL 
  ) A
 ) T
 OUTER APPLY (SELECT C.V.value('.','nvarchar(100)') AS COL_NEW FROM T.COL_NEW.nodes('/root/v') AS C(V) ) T1

 

    到此，已达到想要的效果，大家可以去看看XML和OUTER APPLY的用法，请大家各自测试吧，谢谢！