InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 01:23:20 GMT20http://www.sqlservercentral.com/Forums/Topic1376365-391-1.aspx[quote][b]Eugene Elutin (10/26/2012)[/b][hr][quote]...they are all really close over 1 million records...[/quote]If you want your CLR with Regex to perform well you need to declare your Regex object as static and use Compile option for the pattern. Try changing your CLR to this:[code="other"]public partial class UserDefinedFunctions{ static readonly Regex _regex = new Regex(@"^[^\.]+\.[^\.]+$", RegexOptions.Compiled); [Microsoft.SqlServer.Server.SqlFunction()] public static bool RegexCLR(string input) { return Regex.IsMatch(input); }};[/code] [/quote]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:[quote]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[/quote]and now for NVARCHAR(MAX)[quote]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[/quote]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.Fri, 26 Oct 2012 16:09:19 GMTCapnHectorhttp://www.sqlservercentral.com/Forums/Topic1376365-391-1.aspx[quote]...they are all really close over 1 million records...[/quote]If you want your CLR with Regex to perform well you need to declare your Regex object as static and use Compile option for the pattern. Try changing your CLR to this:[code="other"]public partial class UserDefinedFunctions{ static readonly Regex _regex = new Regex(@"^[^\.]+\.[^\.]+$", RegexOptions.Compiled); [Microsoft.SqlServer.Server.SqlFunction()] public static bool RegexCLR(string input) { return Regex.IsMatch(input); }};[/code] Fri, 26 Oct 2012 08:08:56 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1376365-391-1.aspx[quote][b]jdfletchr (10/26/2012)[/b][hr]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.[/quote]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.Fri, 26 Oct 2012 08:05:09 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1376365-391-1.aspxTry 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.Duration for CHARINDEX = 00:00:00:933Duration for Len = 00:00:00:607Duration for PARSENAME = 00:00:00:127SET NOCOUNT ON;IF object_id('tempdb..#testEnvironment') IS NOT NULLBEGIN DROP TABLE #testEnvironment;END; --1,000,000 Random rows of dataSELECT IDENTITY(INT,1,1) AS ID, CAST(LEFT(yourData, LEN(yourData) - 1) AS varchar(50)) AS yourDataINTO #testEnvironmentFROM (SELECT TOP 1000000 REPLICATE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(NEWID(),'-','') ,'0',''),'9',''),'8',''),'7',''),'6',''),'5',''),'4',''),'3',''),'2',''),'1','') + '.', (ABS(CHECKSUM(NEWID())) % 2) + 2) FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3 )a(yourData); --select * from #testEnvironment where PARSENAME(yourData,3) is null --00:00:48:00DECLARE @HOLDER INT, @Duration CHAR(12), @StartTime DATETIME;SELECT @StartTime = GETDATE();SELECT @HOLDER = IDFROM #testEnvironmentWHERE CHARINDEX('.', yourData, CHARINDEX('.', yourData) + 1) = 0;SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);RAISERROR('Duration for CHARINDEX = %s',0,1,@Duration) WITH NOWAIT;SELECT @StartTime = SYSDATETIME();SELECT @HOLDER = IDFROM #testEnvironmentWHERE LEN(CAST(yourData AS NVARCHAR(MAX)))-1 = LEN(REPLACE(CAST(yourData AS NVARCHAR(MAX)) COLLATE Latin1_General_BIN2,'.',''));SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);RAISERROR('Duration for Len = %s',0,1,@Duration) WITH NOWAIT;SELECT @StartTime = SYSDATETIME();select @HOLDER = ID from #testEnvironment where PARSENAME(yourData,3) is null SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);RAISERROR('Duration for PARSENAME = %s',0,1,@Duration) WITH NOWAIT;Fri, 26 Oct 2012 07:45:27 GMTDaviohttp://www.sqlservercentral.com/Forums/Topic1376365-391-1.aspxso 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.[quote]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[/quote]What i added to the run[code="sql"]DBCC FREEPROCCACHE WITH NO_INFOMSGS;DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;DECLARE @HOLDER INT, @Duration CHAR(12), @StartTime DATETIME;SELECT @StartTime = GETDATE();SELECT @HOLDER = IDFROM #testEnvironmentWHERE dbo.RegexCLR(ISNULL(yourData,'')) = 1SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);RAISERROR('Duration for SQLCLR = %s',0,1,@Duration) WITH NOWAIT;[/code]and the code for the SQLCLR[code="other"]public partial class UserDefinedFunctions{ [Microsoft.SqlServer.Server.SqlFunction()] public static bool RegexCLR(string input) { return Regex.IsMatch(input,@"^[^\.]+\.[^\.]+$"); }};[/code]of course when we change the NTEXT to NVARCHAR(MAX) i get the following:[quote]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[/quote]they are all really close over 1 million records.Thu, 25 Oct 2012 16:37:30 GMTCapnHectorhttp://www.sqlservercentral.com/Forums/Topic1376365-391-1.aspxHi 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.[code="sql"]SELECT @StartTime = GETDATE();SELECT @HOLDER = IDFROM #testEnvironmentWHERE 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 = IDFROM #testEnvironmentWHERE 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;[/code]And got the following[code="plain"]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[/code]Thu, 25 Oct 2012 13:07:24 GMTmickyThttp://www.sqlservercentral.com/Forums/Topic1376365-391-1.aspx[quote][b]Luis Cazares (10/25/2012)[/b][hr]I was so close, thanks to the QotD that made me remember the PARSENAME function.My code[code="sql"]SELECT @HOLDER = IDFROM #testEnvironmentWHERE PARSENAME( CAST(yourData AS NVARCHAR(MAX)),3) IS NULLAND PARSENAME(CAST(yourData AS NVARCHAR(MAX)), 2) IS NOT NULL[/code]Results using Cadavre's last test environment (without the XML). I deleted the "DBCC execution completed" comments.[code="plain"]Duration for CHARINDEX = 00:00:18:363Duration for LEN = 00:00:09:347Duration for PARSENAME = 00:00:06:870Duration for COLLATE = 00:00:06:667[/code][/quote]Very good, that was sort of the idea I was trying to implement with the XML splitter. Didn't think of PARSENAME (foolishly!).Also, we can get rid of the "DBCC execution completed" comments by adding "WITH NO_INFOMSGS" to the script, like so: -[code="sql"]SET NOCOUNT ON;IF object_id('tempdb..#testEnvironment') IS NOT NULLBEGIN DROP TABLE #testEnvironment;END; --1,000,000 Random rows of dataSELECT IDENTITY(INT,1,1) AS ID, CAST(LEFT(yourData, LEN(yourData) - 1) AS NTEXT) AS yourDataINTO #testEnvironmentFROM (SELECT TOP 1000000 REPLICATE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(NEWID(),'-','') ,'0',''),'9',''),'8',''),'7',''),'6',''),'5',''),'4',''),'3',''),'2',''),'1','') + '.', (ABS(CHECKSUM(NEWID())) % 2) + 2) FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3 )a(yourData);DECLARE @HOLDER INT, @Duration CHAR(12), @StartTime DATETIME;DBCC FREEPROCCACHE WITH NO_INFOMSGS;DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;SELECT @StartTime = GETDATE();SELECT @HOLDER = IDFROM (SELECT ID, yourData, MAX(rn) FROM (SELECT ID, yourData, split.Part.value('text()[1]', 'VARCHAR(MAX)'), ROW_NUMBER() OVER(PARTITION BY yourData ORDER BY (SELECT NULL)) FROM (SELECT CAST('<p>' + REPLACE(CAST(yourData AS VARCHAR(MAX)) COLLATE Latin1_General_BIN2,'.','</p><p>') + '</p>' AS XML), CAST(yourData AS VARCHAR(MAX)), ID FROM #testEnvironment) innerQ(xmlField, yourData, ID) CROSS APPLY innerQ.xmlField.nodes('p') split(Part) ) a(ID, yourData,splitData,rn) GROUP BY ID, yourData ) a(ID, yourData, rn)WHERE rn = 2;SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);RAISERROR('Duration for ugly xml split = %s',0,1,@Duration) WITH NOWAIT;DBCC FREEPROCCACHE WITH NO_INFOMSGS;DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;SELECT @StartTime = GETDATE();SELECT @HOLDER = IDFROM #testEnvironmentWHERE CHARINDEX('.', yourData, CHARINDEX('.', yourData) + 1) = 0;SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);RAISERROR('Duration for CHARINDEX = %s',0,1,@Duration) WITH NOWAIT;DBCC FREEPROCCACHE WITH NO_INFOMSGS;DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;SELECT @StartTime = GETDATE();SELECT @HOLDER = IDFROM #testEnvironmentWHERE LEN(CAST(yourData AS NVARCHAR(MAX)))-1 = LEN(REPLACE(CAST(yourData AS NVARCHAR(MAX)) COLLATE Latin1_General_BIN2,'.',''));SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);RAISERROR('Duration for LEN = %s',0,1,@Duration) WITH NOWAIT;DBCC FREEPROCCACHE WITH NO_INFOMSGS;DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;SELECT @StartTime = GETDATE();SELECT @HOLDER = IDFROM #testEnvironmentWHERE CHARINDEX('.', yourData COLLATE Latin1_General_BIN2, CHARINDEX('.', yourData COLLATE Latin1_General_BIN2) + 1) = 0;SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);RAISERROR('Duration for CHARINDEX with COLLATE = %s',0,1,@Duration) WITH NOWAIT;DBCC FREEPROCCACHE WITH NO_INFOMSGS;DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;SELECT @StartTime = GETDATE();SELECT @HOLDER = IDFROM #testEnvironmentWHERE PARSENAME(CAST(yourData AS NVARCHAR(MAX)),3) IS NULLAND PARSENAME(CAST(yourData AS NVARCHAR(MAX)), 2) IS NOT NULL;SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);RAISERROR('Duration for PARSENAME = %s',0,1,@Duration) WITH NOWAIT;[/code][code="plain"]Duration for ugly xml split = 00:00:47:197Duration for CHARINDEX = 00:00:07:593Duration for LEN = 00:00:08:163Duration for CHARINDEX with COLLATE = 00:00:04:787Duration for PARSENAME = 00:00:05:193[/code][quote][b]Luis Cazares (10/25/2012)[/b][hr][quote][b]Davin21 (10/25/2012)[/b][hr]How about this?[code="sql"]SELECT *FROM #testenvironmentWHERE PARSENAME(CAST(yourdata AS NVARCHAR(MAX)),3) IS NULL[/code][/quote]I had the same idea, but I added an extra condition because your query will return results with no period (or dot) and with more than 3.[/quote]I've got the agree with Luis, it's a good idea but it could return incorrect results.Thu, 25 Oct 2012 08:29:16 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1376365-391-1.aspx[quote][b]Davin21 (10/25/2012)[/b][hr]How about this?[code="sql"]SELECT *FROM #testenvironmentWHERE PARSENAME(CAST(yourdata AS NVARCHAR(MAX)),3) IS NULL[/code][/quote]I had the same idea, but I added an extra condition because your query will return results with no period (or dot) and with more than 3.Thu, 25 Oct 2012 08:24:33 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1376365-391-1.aspxI was so close, thanks to the QotD that made me remember the PARSENAME function.My code[code="sql"]SELECT @HOLDER = IDFROM #testEnvironmentWHERE PARSENAME( CAST(yourData AS NVARCHAR(MAX)),3) IS NULLAND PARSENAME(CAST(yourData AS NVARCHAR(MAX)), 2) IS NOT NULL[/code]Results using Cadavre's last test environment (without the XML). I deleted the "DBCC execution completed" comments.[code="plain"]Duration for CHARINDEX = 00:00:18:363Duration for LEN = 00:00:09:347Duration for PARSENAME = 00:00:06:870Duration for COLLATE = 00:00:06:667[/code]Thu, 25 Oct 2012 08:20:24 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1376365-391-1.aspxHow about this?[code="sql"]SELECT *FROM #testenvironmentWHERE PARSENAME(CAST(yourdata AS NVARCHAR(MAX)),3) IS NULL[/code]Thu, 25 Oct 2012 08:15:59 GMTDavin21http://www.sqlservercentral.com/Forums/Topic1376365-391-1.aspxGood idea Mark. I also added an ugly XML split to count the number of "items", then modified the test script to clear the cache between each piece of code to attempt to keep it fairer.[code="sql"]SET NOCOUNT ON;IF object_id('tempdb..#testEnvironment') IS NOT NULLBEGIN DROP TABLE #testEnvironment;END; --1,000,000 Random rows of dataSELECT IDENTITY(INT,1,1) AS ID, CAST(LEFT(yourData, LEN(yourData) - 1) AS NTEXT) AS yourDataINTO #testEnvironmentFROM (SELECT TOP 1000000 REPLICATE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(NEWID(),'-','') ,'0',''),'9',''),'8',''),'7',''),'6',''),'5',''),'4',''),'3',''),'2',''),'1','') + '.', (ABS(CHECKSUM(NEWID())) % 2) + 2) FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3 )a(yourData);DECLARE @HOLDER INT, @Duration CHAR(12), @StartTime DATETIME;DBCC FREEPROCCACHE;DBCC DROPCLEANBUFFERS;SELECT @StartTime = GETDATE();SELECT @HOLDER = IDFROM (SELECT ID, yourData, MAX(rn) FROM (SELECT ID, yourData, split.Part.value('text()[1]', 'VARCHAR(MAX)'), ROW_NUMBER() OVER(PARTITION BY yourData ORDER BY (SELECT NULL)) FROM (SELECT CAST('<p>' + REPLACE(CAST(yourData AS VARCHAR(MAX)) COLLATE Latin1_General_BIN2,'.','</p><p>') + '</p>' AS XML), CAST(yourData AS VARCHAR(MAX)), ID FROM #testEnvironment) innerQ(xmlField, yourData, ID) CROSS APPLY innerQ.xmlField.nodes('p') split(Part) ) a(ID, yourData,splitData,rn) GROUP BY ID, yourData ) a(ID, yourData, rn)WHERE rn = 2;SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);RAISERROR('Duration for ugly xml split = %s',0,1,@Duration) WITH NOWAIT;DBCC FREEPROCCACHE;DBCC DROPCLEANBUFFERS;SELECT @StartTime = GETDATE();SELECT @HOLDER = IDFROM #testEnvironmentWHERE CHARINDEX('.', yourData, CHARINDEX('.', yourData) + 1) = 0;SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);RAISERROR('Duration for CHARINDEX = %s',0,1,@Duration) WITH NOWAIT;DBCC FREEPROCCACHE;DBCC DROPCLEANBUFFERS;SELECT @StartTime = GETDATE();SELECT @HOLDER = IDFROM #testEnvironmentWHERE LEN(CAST(yourData AS NVARCHAR(MAX)))-1 = LEN(REPLACE(CAST(yourData AS NVARCHAR(MAX)) COLLATE Latin1_General_BIN2,'.',''));SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);RAISERROR('Duration for LEN = %s',0,1,@Duration) WITH NOWAIT;DBCC FREEPROCCACHE;DBCC DROPCLEANBUFFERS;SELECT @StartTime = GETDATE();SELECT @HOLDER = IDFROM #testEnvironmentWHERE CHARINDEX('.', yourData COLLATE Latin1_General_BIN2 , CHARINDEX('.', yourData COLLATE Latin1_General_BIN2) + 1) = 0;SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);RAISERROR('Duration for CHARINDEX with COLLATE = %s',0,1,@Duration) WITH NOWAIT;[/code][code="plain"]DBCC execution completed. If DBCC printed error messages, contact your system administrator.DBCC execution completed. If DBCC printed error messages, contact your system administrator.Duration for ugly xml split = 00:00:48:980DBCC execution completed. If DBCC printed error messages, contact your system administrator.DBCC execution completed. If DBCC printed error messages, contact your system administrator.Duration for CHARINDEX = 00:00:08:640DBCC execution completed. If DBCC printed error messages, contact your system administrator.DBCC execution completed. If DBCC printed error messages, contact your system administrator.Duration for LEN = 00:00:09:087DBCC execution completed. If DBCC printed error messages, contact your system administrator.DBCC execution completed. If DBCC printed error messages, contact your system administrator.Duration for CHARINDEX with COLLATE = 00:00:05:323[/code]So the CHARINDEX with COLLATE that Mark suggested is the fastest so far - whilst my ugly XML splitter proves its worth as considerably slower :-DThu, 25 Oct 2012 07:21:54 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1376365-391-1.aspxA mix of the two seems to help[code="sql"]SELECT @HOLDER = IDFROM #testEnvironmentWHERE CHARINDEX('.', yourData COLLATE Latin1_General_BIN2 , CHARINDEX('.', yourData COLLATE Latin1_General_BIN2) + 1) = 0;[/code]Thu, 25 Oct 2012 06:56:16 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic1376365-391-1.aspxTwo solutions. . . this means performance check!! :-)Here's 1 million rows of sample data: -[code="sql"]SET NOCOUNT ON;IF object_id('tempdb..#testEnvironment') IS NOT NULLBEGIN DROP TABLE #testEnvironment;END; --1,000,000 Random rows of dataSELECT IDENTITY(INT,1,1) AS ID, CAST(LEFT(yourData, LEN(yourData) - 1) AS NTEXT) AS yourDataINTO #testEnvironmentFROM (SELECT TOP 1000000 REPLICATE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(NEWID(),'-','') ,'0',''),'9',''),'8',''),'7',''),'6',''),'5',''),'4',''),'3',''),'2',''),'1','') + '.', (ABS(CHECKSUM(NEWID())) % 2) + 2) FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3 )a(yourData);[/code][code="sql"]DECLARE @HOLDER INT, @Duration CHAR(12), @StartTime DATETIME;SELECT @StartTime = GETDATE();SELECT @HOLDER = IDFROM #testEnvironmentWHERE CHARINDEX('.', yourData, CHARINDEX('.', yourData) + 1) = 0;SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);RAISERROR('Duration for CHARINDEX = %s',0,1,@Duration) WITH NOWAIT;SELECT @StartTime = SYSDATETIME();SELECT @HOLDER = IDFROM #testEnvironmentWHERE LEN(CAST(yourData AS NVARCHAR(MAX)))-1 = LEN(REPLACE(CAST(yourData AS NVARCHAR(MAX)) COLLATE Latin1_General_BIN2,'.',''));SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);RAISERROR('Duration for LEN = %s',0,1,@Duration) WITH NOWAIT;[/code][code="plain"]Duration for CHARINDEX = 00:00:10:660Duration for LEN = 00:00:06:327[/code]Second run (I recreated the test environment between each run):-[code="sql"]DECLARE @HOLDER INT, @Duration CHAR(12), @StartTime DATETIME;SELECT @StartTime = SYSDATETIME();SELECT @HOLDER = IDFROM #testEnvironmentWHERE LEN(CAST(yourData AS NVARCHAR(MAX)))-1 = LEN(REPLACE(CAST(yourData AS NVARCHAR(MAX)) COLLATE Latin1_General_BIN2,'.',''));SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);RAISERROR('Duration for LEN = %s',0,1,@Duration) WITH NOWAIT;SELECT @StartTime = GETDATE();SELECT @HOLDER = IDFROM #testEnvironmentWHERE CHARINDEX('.', yourData, CHARINDEX('.', yourData) + 1) = 0;SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);RAISERROR('Duration for CHARINDEX = %s',0,1,@Duration) WITH NOWAIT;[/code][code="plain"]Duration for LEN = 00:00:10:927Duration for CHARINDEX = 00:00:05:900[/code]So the CHARINDEX function looks better, but both are pretty slow. Anyone got a better way?Thu, 25 Oct 2012 06:47:51 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1376365-391-1.aspxsecond option for the same requiment[code="sql"]declare @temp table(t varchar(50))insert into @tempselect * from(values('a.p'),('y.a.p'),('k.p'))a (name)select *from @tempwhere CHARINDEX('.',t,CHARINDEX('.',t)+1)=0[/code]Thu, 25 Oct 2012 04:07:07 GMTBriPanhttp://www.sqlservercentral.com/Forums/Topic1376365-391-1.aspx[quote][b]davdam8 (10/24/2012)[/b][hr]WOW!Works great!Thanks body!Regards[/quote]And if at all possible change your ntext to nvarchar(max). ntext is deprecated and complete PITA to work with.Wed, 24 Oct 2012 08:08:40 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1376365-391-1.aspxWOW!Works great!Thanks body!RegardsWed, 24 Oct 2012 03:26:37 GMTdavdam8http://www.sqlservercentral.com/Forums/Topic1376365-391-1.aspx[quote][b]davdam8 (10/24/2012)[/b][hr]ThanksIt gives me an error: [i]Argument data type ntext is invalid for argument 1 of len function[/i]Solutions?Regards[/quote]That would be because you didn't supply DDL or readily consumable sample data, which caused me to assume that you were not using a deprecated type.You'll have to cast it to NVARCHAR or VARCHAR to use, e.g.[code="sql"]SELECT *FROM #yourTableWHERE LEN(CAST(yourData AS NVARCHAR(MAX)))-1 = LEN(REPLACE(CAST(yourData AS NVARCHAR(MAX)) COLLATE Latin1_General_BIN2,'.',''));[/code]Wed, 24 Oct 2012 03:20:14 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1376365-391-1.aspxThanksIt gives me an error: [i]Argument data type ntext is invalid for argument 1 of len function[/i]Solutions?RegardsWed, 24 Oct 2012 03:08:22 GMTdavdam8http://www.sqlservercentral.com/Forums/Topic1376365-391-1.aspx[code="sql"]--========================----== CREATE SAMPLE DATA ==----========================--SELECT yourDataINTO #yourTableFROM (VALUES('MNN.helloworld'),('GFF.goodbyeworld'),('SSW.seeyoulaterworld.oops') )a(yourData); --========================----== SOLUTION ==----========================--SELECT *FROM #yourTableWHERE LEN(yourData)-1 = LEN(REPLACE(yourData COLLATE Latin1_General_BIN2,'.',''));[/code]Wed, 24 Oct 2012 03:02:19 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1376365-391-1.aspxHi geniuses!Hi have a field (VALUE), which contains data like:MNN.helloworldGFF.goodbyeworldSSW.seeyoulaterworld.oopsAnd I want to retrieve data when there's only one '.' in the data.Is this doable?ThanksRegardsWed, 24 Oct 2012 02:56:40 GMTdavdam8