Here is the testing:

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

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('%[0-9][0-9][0-9][0-9]%',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: %[0-9][0-9][0-9][0-9]% -----';

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

The results:

Beginning execution loop

Batch execution completed 10000 times.

----- Using DelimitedSplit/CROSS APPLY -----

CPU(ms): 407 Logical Reads: 80564 Elapsed(ms): 1616 Reads: 80 Writes: 0

----- Using PATINDEX: %[^,][^,][^,][^,]% -----

CPU(ms): 109 Logical Reads: 245 Elapsed(ms): 108 Reads: 0 Writes: 0

----- Using PATINDEX: %[0-9][0-9][0-9][0-9]% -----

CPU(ms): 125 Logical Reads: 245 Elapsed(ms): 116 Reads: 0 Writes: 0