分享下用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的用法,请大家各自测试吧,谢谢!