在 SQL Server 中的两个分隔符之间提取数据?

Question

我有一列包含aaa|bbb|ccc格式的数据，需要分别从数据中提取aaa，bbb和ccc。

我试过了

SELECT  
    SUBSTRING(Column1, 0, CHARINDEX('|', Column1)) AS [First],
    SUBSTRING(Column1, CHARINDEX('|', Column1)  + 1, LEN(Column1)) AS [Second]
FROM
    Table1

输出：

aaa [FIRST],bbb|ccc [Second] 

但我需要aaa [FIRST],bbb [Second],ccc [Third]

Answer 1

如果你有一个由|分隔的完全相同格式(三次(的字符串，那么你可以 PARSENAME(( ：

select col1, parsename(cols, 3) fisrt, parsename(cols, 2) second, parsename(cols, 1) third
from table1 t1 cross apply
     ( values (replace(col1, '|', '.')) 
     ) t2 (cols);

Answer 2

您可以在下面尝试使用函数charindex、SUBSTRING和REVERSE的方式

 with t as (select 'aaa|bbb|ccc' as val)
select t.*,
       LEFT(val, charindex('|', val) - 1),
   SUBSTRING(val, charindex('|', val)+1, len(val) - CHARINDEX('|', reverse(val)) - charindex('|', val)),
       REVERSE(LEFT(reverse(val), charindex('|', reverse(val)) - 1))
from t;
val             
aaa|bbb|ccc     aaa     bbb     ccc

Answer 3

您可以使用公用表表达式 (CTE( 拆分列中的值，因为拆分函数在您的 SQL Server 版本中不可用

WITH CTE(userString,startIndex,EndIndex)
AS
(
    SELECT Column1,1,CHARINDEX('|',Column1)
    FROM Table1
    UNION ALL
    SELECT Column1, EndIndex+1,CHARINDEX('|',Column1,EndIndex+1)
    FROM CTE
    WHERE EndIndex !=0 
)
SELECT SUBSTRING(userString,
                startIndex,
                CASE WHEN EndIndex > 0 
                THEN EndIndex - startIndex ELSE LEN(Column1) END) 
       as splitVALUES
FROM CTE

Answer 4

我一直这样做，它对我来说效果很好：

DECLARE @delimString VARCHAR(255) = 'aaa|bbb|ccc';
DECLARE @xml XML = '<val>' + REPLACE( @delimString, '|', '</val><val>' ) + '</val>'
SELECT 
    x.f.value( '.', 'VARCHAR(50)' ) AS val
FROM @xml.nodes( '//val' ) x( f );

返回

+-----+
| val |
+-----+
| aaa |
| bbb |
| ccc |
+-----+

如果您正在寻找列式返回，并且知道您始终只有三个值需要解析，那么您可以使用以下示例。可以在 SSMS 中运行它。

DECLARE @table TABLE ( [value] VARCHAR(255) );
INSERT INTO @table ( [value] ) VALUES 
( 'aaa|bbb|ccc' )
, ( '0A-PRDS|JQLM-1|1967' )
, ( 'J1658|G-1|2003' );
SELECT
    [value]
    , SUBSTRING( [value], 0, CHARINDEX( '|', [value] ) ) AS Column1
    , SUBSTRING( 
        [value]
        , ( CHARINDEX( '|', [value]) + 1 )  -- starting position of column 2.
        , CHARINDEX( '|', [value],  ( CHARINDEX( '|', [value] ) + 1 ) ) - ( CHARINDEX( '|', [value]) + 1 ) -- length of column two is the number of characters between the two delimiters.
    ) AS Column2
    , SUBSTRING(
        [value]
        , CHARINDEX( '|', [value], ( CHARINDEX( '|', [value] ) + 1 ) ) + 1
        , LEN( [value] )
    ) AS Column3
FROM @table;

返回

+---------------------+---------+---------+---------+
|        value        | Column1 | Column2 | Column3 |
+---------------------+---------+---------+---------+
| aaa|bbb|ccc         | aaa     | bbb     | ccc     |
| 0A-PRDS|JQLM-1|1967 | 0A-PRDS | JQLM-1  | 1967    |
| J1658|G-1|2003      | J1658   | G-1     | 2003    |
+---------------------+---------+---------+---------+