I was doing some testing, code below:
set nocount on;
-- sample data
IF OBJECT_ID('tempdb..#x') IS NOT NULL
DROP TABLE #x;
CREATE TABLE #x (id int identity primary key, val nvarchar(100) NOT NULL);
GO
INSERT INTO #x
SELECT '55,85,1,4,9888,6587' UNION ALL --we want this one
SELECT '55,85,1,4,98,65' UNION ALL --we don't want this
SELECT '1122,33333,22,11,40' UNION ALL --we want this one
SELECT '312,9,8,7' --we don't want this
GO 10000
set nocount on;
--===== Create the timer and bit-bucket variables and start the timer.
DECLARE @Bitbucket nvarchar(100); --< change or add variables needed here to eliminate display distortion
Declare @cpu_ int,
@lreads_ bigint,
@eMsec_ bigint,
@Reads_ bigint,
@Writes_ bigint;
declare @CpuMs int,
@LogRds bigint,
@Elapsed bigint,
@Reads bigint,
@Writes bigint;
dbcc freeproccache with no_infomsgs;
dbcc freesystemcache('ALL') with no_infomsgs;
dbcc dropcleanbuffers with no_infomsgs;
Select
@cpu_ = cpu_time
, @lreads_ = logical_reads
, @eMsec_ = total_elapsed_time
, @Reads_ = reads
, @Writes_ = writes
From
sys.dm_exec_requests
Where
session_id = @@spid;
---
WITH legitIDs AS
(SELECT id
FROM #x x
CROSS APPLY dbo.DelimitedSplit8K(x.val,',')
WHERE LEN(item)>=4
GROUP BY id)
SELECT
@Bitbucket = val
FROM
#x x
JOIN legitIDs l ON x.id=l.id;
---
Select
@CpuMs = cpu_time - @cpu_
, @LogRds = logical_reads - @lreads_
, @Elapsed = total_elapsed_time - @eMsec_
, @Reads = reads - @Reads_
, @Writes = writes - @Writes_
From
sys.dm_exec_requests
Where
session_id = @@spid;
--===== Display the duration
PRINT '----- Using DelimitedSplit/CROSS APPLY -----';
PRINT 'CPU(ms): ' + right(' ' + cast(@CpuMs as varchar(10)),10) +
' Logical Reads: ' + right(' ' + cast(@LogRds as varchar(10)),10) +
' Elapsed(ms): ' + right(' ' + cast(@Elapsed as varchar(10)),10) +
' Reads: ' + right(' ' + cast(@Reads as varchar(10)),10) +
' Writes: ' + right(' ' + cast(@Writes as varchar(10)),10);
set nocount off;
GO
set nocount on;
--===== Create the timer and bit-bucket variables and start the timer.
DECLARE @Bitbucket nvarchar(100); --< change or add variables needed here to eliminate display distortion
Declare @cpu_ int,
@lreads_ bigint,
@eMsec_ bigint,
@Reads_ bigint,
@Writes_ bigint;
declare @CpuMs int,
@LogRds bigint,
@Elapsed bigint,
@Reads bigint,
@Writes bigint;
dbcc freeproccache with no_infomsgs;
dbcc freesystemcache('ALL') with no_infomsgs;
dbcc dropcleanbuffers with no_infomsgs;
Select
@cpu_ = cpu_time
, @lreads_ = logical_reads
, @eMsec_ = total_elapsed_time
, @Reads_ = reads
, @Writes_ = writes
From
sys.dm_exec_requests
Where
session_id = @@spid;
---
select
@Bitbucket = val
FROM
#x x
where
patindex('%[^,][^,][^,][^,]%',val) > 0;
---
Select
@CpuMs = cpu_time - @cpu_
, @LogRds = logical_reads - @lreads_
, @Elapsed = total_elapsed_time - @eMsec_
, @Reads = reads - @Reads_
, @Writes = writes - @Writes_
From
sys.dm_exec_requests
Where
session_id = @@spid;
--===== Display the duration
PRINT '----- Using PATINDEX -----';
PRINT 'CPU(ms): ' + right(' ' + cast(@CpuMs as varchar(10)),10) +
' Logical Reads: ' + right(' ' + cast(@LogRds as varchar(10)),10) +
' Elapsed(ms): ' + right(' ' + cast(@Elapsed as varchar(10)),10) +
' Reads: ' + right(' ' + cast(@Reads as varchar(10)),10) +
' Writes: ' + right(' ' + cast(@Writes as varchar(10)),10);
set nocount off;
GO
--cleanup
DROP TABLE #x;
GO
Results:
Beginning execution loop
Batch execution completed 10000 times.
----- Using DelimitedSplit/CROSS APPLY -----
CPU(ms): 422 Logical Reads: 80564 Elapsed(ms): 1667 Reads: 80 Writes: 0
----- Using PATINDEX -----
CPU(ms): 109 Logical Reads: 245 Elapsed(ms): 110 Reads: 0 Writes: 0