Hi

I thought I would add in a LIKE query to see how that compared. It wasn't as bad as I thought it would be.

SELECT @StartTime = GETDATE();

SELECT @HOLDER = ID

FROM #testEnvironment

WHERE yourData like '%.%' and yourData not like '%.%.%';

SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);

RAISERROR('Duration for LIKE = %s',0,1,@Duration) WITH NOWAIT;

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

SELECT @StartTime = GETDATE();

SELECT @HOLDER = ID

FROM #testEnvironment

WHERE yourData COLLATE Latin1_General_BIN2 like '%.%' and yourData COLLATE Latin1_General_BIN2 not like '%.%.%';

SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);

RAISERROR('Duration for LIKE = %s',0,1,@Duration) WITH NOWAIT;

And got the following

Duration for CHARINDEX = 00:00:09:103

Duration for LEN = 00:00:10:790

Duration for CHARINDEX with COLLATE = 00:00:06:507

Duration for PARSENAME = 00:00:07:717

Duration for LIKE = 00:00:10:703

Duration for LIKE with COLLATE = 00:00:06:517