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:103Duration for LEN = 00:00:10:790Duration for CHARINDEX with COLLATE = 00:00:06:507Duration for PARSENAME = 00:00:07:717Duration for LIKE = 00:00:10:703Duration for LIKE with COLLATE = 00:00:06:517

so i went for the SQL CLR just to see if i could get it to work (been on a learning kick right now so im going with it) and here are my results.

Duration for CHARINDEX = 00:00:21:963Duration for LEN = 00:00:16:217Duration for CHARINDEX with COLLATE = 00:00:10:260Duration for PARSENAME = 00:00:11:043Duration for LIKE = 00:00:11:293Duration for LIKE with Collate = 00:00:10:707Duration for SQLCLR = 00:00:14:427

of course when we change the NTEXT to NVARCHAR(MAX) i get the following:

Duration for CHARINDEX = 00:00:06:030Duration for LEN = 00:00:03:253Duration for CHARINDEX with COLLATE = 00:00:00:943Duration for PARSENAME = 00:00:01:147Duration for LIKE = 00:00:07:253Duration for LIKE with Collate = 00:00:01:353Duration for SQLCLR = 00:00:04:040

jdfletchr (10/26/2012)Try the Parsename function. Not what is was made for, but works quite well for what you need and is much faster that CharIndex and Len.

Did you read the thread with the tests made and the observation on a solultion that was the same as yours?Without an extra condition, your PARSENAME solution will throw incorrect results.By the way, another problem is we're dealing with an ntext column.

Luis C.General Disclaimer:Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Thanks for the tip on the regex im new to C# but chose to learn that language specifically for CLR's. after the code change and rerunning the tests this is what i got with NTEXT:

Duration for CHARINDEX = 00:00:14:957Duration for LEN = 00:00:15:723Duration for CHARINDEX with COLLATE = 00:00:09:790Duration for PARSENAME = 00:00:10:717Duration for LIKE = 00:00:11:087Duration for LIKE with COLLATE = 00:00:10:683Duration for SQLCLR = 00:00:11:427

and now for NVARCHAR(MAX)

Duration for CHARINDEX = 00:00:06:253Duration for LEN = 00:00:03:200Duration for CHARINDEX with COLLATE = 00:00:01:033Duration for PARSENAME = 00:00:01:327Duration for LIKE = 00:00:07:237Duration for LIKE with COLLATE = 00:00:01:457Duration for SQLCLR = 00:00:01:953

Im actually supprised that by changing the datatype we can chop a factor of 10 off the execution times. never really saw the direct impact of data types like this before.