从SQL字符串分割多桩分隔符

How to Split SQL string with (# and ;)?

如何使用(#和;)分割SQL字符串?

Billing Column where string is saved in below format

账单列，其中字符串保存为以下格式

548784545S#15/01/2016;84854555545#13/01/2016;45454554545#21/01/2016

Split # and ; I want below format

分手#;我想要下面的格式

548784545S    15/01/2016
84854555545  13/01/2016
45454554545  21/01/2016

So Far what i did

到目前为止我所做的

SELECT SUBSTRING(CNT_SHIPPING_BILLNO, 1,
            CASE CHARINDEX('#', CNT_SHIPPING_BILLNO)
               WHEN 0
                   THEN LEN(CNT_SHIPPING_BILLNO)
               ELSE CHARINDEX('#', CNT_SHIPPING_BILLNO) - 1
           END)
       AS Billno,
       SUBSTRING(CNT_SHIPPING_BILLNO,
           CASE CHARINDEX('#', CNT_SHIPPING_BILLNO)
               WHEN 0
                   THEN LEN(CNT_SHIPPING_BILLNO) + 1
               ELSE CHARINDEX(';', CNT_SHIPPING_BILLNO) + 1
           END, 1000) AS BillDate
FROM SHIPPINGBILL

Here I am getting only initial Value using above Query

这里我只使用上面的查询获得初始值

548784545S    15/01/2016;84854555545#13/01/2016;45454554545#21/01/2016

5 个解决方案

#1

DECLARE @t VARCHAR(MAX) = '548784545S#15/01/2016;84854555545#13/01/2016;45454554545#21/01/2016'

;WITH cte AS (
    SELECT x = CONVERT(XML,'<p>' + REPLACE(@t, ';', '</p><p>') + '</p>')
)
SELECT PARSENAME(val, 2), PARSENAME(val, 1)
FROM (
    SELECT val = REPLACE(t.c.value('.', 'VARCHAR(MAX)'), '#', '.')
    FROM cte
    CROSS APPLY x.nodes('p') t(c)
) t

Output -

输出-

--------------- ------------
548784545S      15/01/2016
84854555545     13/01/2016
45454554545     21/01/2016

5 个解决方案

#1

更多相关文章

随机推荐