在SQLServer中使用CHARINDEX函式的問題

有人可以幫助我嗎？我試圖在我的分隔列中獲取特定值。

Column_A 是我的資料 Column_B 是我可以得到的 Column_C 是我想要的

基本上我試圖獲得第三個“：”和第四個“：”之間的值

我在這里使用這段代碼：

select SourceID
     , SUBSTRING(SourceID,CHARINDEX(':', SourceID, CHARINDEX(':', SourceID)   1)   1,
       CHARINDEX(':', SourceID, CHARINDEX(':', SourceID, CHARINDEX(':', SourceID)   1)   1) -6)
 from  temp.table

提前致謝

uj5u.com熱心網友回復：

您可以嘗試使用遞回 CTE 根據需要檢索字串的任何部分。像這樣的東西

CREATE TABLE #Temp
(
    MyString NVARCHAR(2000)
)

INSERT INTO #TEMP
VALUES('42:45:ABCD:GGRFG34:SADSAD'),('65:213:5435423:234234')

;WITH CTE AS
(
    SELECT
        ParentSTring = MyString,
        MyString = CASE CHARINDEX(':',MyString) WHEN 0 THEN NULL ELSE SUBSTRING(MyString,CHARINDEX(':',MyString) 1,LEN(MyString)) END,
        Part = CASE CHARINDEX(':',MyString) WHEN 0 THEN MyString ELSE SUBSTRING(MyString,1,CHARINDEX(':',MyString)-1) END,
        Seq = 1
    FROM 
        #Temp

    UNION ALL

    SELECT
        ParentSTring,
        MyString = CASE CHARINDEX(':',MyString) WHEN 0 THEN NULL ELSE SUBSTRING(MyString,CHARINDEX(':',MyString) 1,LEN(MyString)) END,
        Part = CASE CHARINDEX(':',MyString) WHEN 0 THEN MyString ELSE SUBSTRING(MyString,1,CHARINDEX(':',MyString)-1) END,
        Seq = ISNULL(Seq,0) 1
    FROM 
        CTE
    WHERE 
        ISNULL(MyString, '') <> ''
)
SELECT
    *
FROM 
    CTE
WHERE 
    Seq = 3 -- for retrieving the 3rd string, change this accordingly

結果

uj5u.com熱心網友回復：

首先，如果性能很重要，那么遞回 CTE 不是您想要的。我馬上演示為什么。

我在這里有一個簡單的解決方案，稱為 SubstringBetween8K，但對于您正在做的事情來說太過分了。為此，一個簡單的Cascading APPLY將發揮作用并發揮最佳效果。首先是樣本資料：

IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp;
GO
CREATE TABLE #temp (SourceId VARCHAR(1000));
INSERT #temp VALUES('42:45:10856x2019035x1200:GGRFG34:SADSAD.9999999999999999'),
                   ('65:213:999555x2019035x9444:5435423:234234,123123.111'),
                   ('999:12344:5555511056x35x9111:5435423:234234,555555555555'),
                   ('225:0:11056x2019035x9444:5435423:ABAFLHG.882');

接下來是 Cascading APPLY 解決方案。

SELECT      Item = SUBSTRING(t.SourceId,  f2.Pos 1, f3.Pos-f2.Pos-1)
FROM        #temp AS t
CROSS APPLY (VALUES(CHARINDEX(':',t.SourceId)))          AS f1(Pos)
CROSS APPLY (VALUES(CHARINDEX(':',t.SourceId,f1.Pos 1))) AS f2(Pos)
CROSS APPLY (VALUES(CHARINDEX(':',t.SourceId,f2.Pos 1))) AS f3(Pos);

結果：

Item
------------------------
10856x2019035x1200
999555x2019035x9444
5555511056x35x9111
11056x2019035x9444

現在進行一個快速的性能測驗，它將演示為什么不使用遞回 CTE。

--==== Sample data
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp;
GO
CREATE TABLE #temp (SourceId VARCHAR(1000));
INSERT #temp VALUES('42:45:10856x2019035x1200:GGRFG34:SADSAD.9999999999999999'),
                   ('65:213:999555x2019035x9444:5435423:234234,123123.111'),
                   ('999:12344:5555511056x35x9111:5435423:234234,555555555555'),
                   ('225:0:11056x2019035x9444:5435423:ABAFLHG.882');

--==== Add 10K rows for performance testing
INSERT #temp
SELECT TOP (100000) sourceId 
FROM   #temp
CROSS JOIN sys.all_columns, sys.all_columns AS b
GO

--==== Performance Test
IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1;
IF OBJECT_ID('tempdb..#t2') IS NOT NULL DROP TABLE #t2;
GO

SET STATISTICS TIME, IO ON;

PRINT CHAR(10) 'Cascading CTE' CHAR(10) REPLICATE('-',90);

SELECT      Item = SUBSTRING(t.SourceId,  f2.Pos 1, f3.Pos-f2.Pos-1)
INTO        #t1
FROM        #temp AS t
CROSS APPLY (VALUES(CHARINDEX(':',t.SourceId)))          AS f1(Pos)
CROSS APPLY (VALUES(CHARINDEX(':',t.SourceId,f1.Pos 1))) AS f2(Pos)
CROSS APPLY (VALUES(CHARINDEX(':',t.SourceId,f2.Pos 1))) AS f3(Pos);


PRINT CHAR(10) 'Recursive CTE' CHAR(10) REPLICATE('-',90);
;WITH CTE AS
(
    SELECT
        ParentSTring = SourceId,
        SourceId = CASE CHARINDEX(':',SourceId) WHEN 0 THEN NULL ELSE SUBSTRING(SourceId,CHARINDEX(':',SourceId) 1,LEN(SourceId)) END,
        Part = CASE CHARINDEX(':',SourceId) WHEN 0 THEN SourceId ELSE SUBSTRING(SourceId,1,CHARINDEX(':',SourceId)-1) END,
        Seq = 1
    FROM  #temp 
    UNION ALL
    SELECT
        ParentSTring,
        MyString = CASE CHARINDEX(':',SourceId) WHEN 0 THEN NULL ELSE SUBSTRING(SourceId,CHARINDEX(':',SourceId) 1,LEN(SourceId)) END,
        Part = CASE CHARINDEX(':',SourceId) WHEN 0 THEN SourceId ELSE SUBSTRING(SourceId,1,CHARINDEX(':',SourceId)-1) END,
        Seq = ISNULL(Seq,0) 1
    FROM  CTE
    WHERE ISNULL(SourceId, '') <> ''
)
SELECT Part
INTO   #t2
FROM   CTE
WHERE  Seq = 3

SET STATISTICS TIME, IO OFF;

檢測結果：

Cascading CTE
------------------------------------------------------------------------------------------
Table '#temp'. Scan count 9, logical reads 807, physical reads 0...

 SQL Server Execution Times: CPU time = 327 ms,  elapsed time = 111 ms.

Recursive CTE
------------------------------------------------------------------------------------------
Table 'Worktable'. Scan count 2, logical reads 4221845, physical reads 0...
Table '#temp'. Scan count 1, logical reads 807, physical reads 0...

 SQL Server Execution Times: CPU time = 8781 ms,  elapsed time = 9370 ms.

從 1/10 秒開始，從 10 秒開始。大約 100 倍的性能提升。遞回 CTE 的部分問題是過多的 IO（讀取）。請注意簡單的 10K 行的 430 萬次讀取。

標籤：sql sql-server 子串 字符索引

上一篇：為什么“DEFAULT”會導致語法問題

下一篇：相關物體的Linq集團