[snap] Unicode values in varbinary(max) columns

When storing Unicode data in varbinary columns the binary stream must begin with the appropriate marker (0xFFFE), otherwise the iFilter will not be able to properly parse the content when building a full-text index. I've learned this the hard way. Well, it didn't actually hurt, but it took a day or two to get cleared up. The interesting bit is the fact that when Unicode data is cast to XML prior to storing it in a varbinary(max) column, the stream is properly marked automatically. This made me think the whole thing was a bug, now it proves to be "by design". One new lesson learned: Need to construct unicode file properly.

Microsoft SQL Server 2005 SP1 CTP

Microsoft SQL Server 2005 Service Pack 1 Community Technology Preview has been announced yesterday and is now publicly available for download. Of course there's also SQL Server 2005 Books Online SP1 CTP, but more importantly SQL Server Express with Advanced Services CTP! SQL Server Express with Advanced Services CTP includes:

• SQL Server Management Studio Express (SSMSE);
• support for full-text catalogs;
• support for viewing reports via report server; and
• SP1.

The SQL Server Express Edition Toolkit is also available, and includes management tools and resources, and even allows creating reports by using SQL Server 2005 Reporting Services. Sounds very promissing. It may, however, take a while to download... ;) ML p.s. Quoted from the publisher: "These packages have been made available for general testing purposes only. Do not deploy the CTP software in production."

More fun with strings

This time the fun is in counting how many times one string occurs inside another. Of course this has been attempted many times - kudos to all who've attempted it before me! My function, however, includes an extra parameter to support case-sensitive comparisons. Here it is:

create function dbo.fnCount_StringInText
 (
 @text  nvarchar(4000)
 ,@string nvarchar(4000)
 ,@caseSensitive bit  = null
 )
returns int
as
begin
 declare @count int

 if (datalength(@string) != 0)
  begin
   if (@caseSensitive = 0 or @caseSensitive is null)
    begin
     set @count
       = (datalength(@text)
       - datalength(replace(@text collate Latin1_General_CI_AS, @string, N'')))
       / datalength(@string)
    end
   else
    begin
     set @count
       = (datalength(@text)
       - datalength(replace(@text collate Latin1_General_CS_AS, @string, N'')))
       / datalength(@string)
    end
  end
 else
  begin
   set @count = 0
  end

 return @count
end
go

A few examples of use:

declare @text varchar(4000)
set @text = N'Round the rough and rugged rocks Roscoe the rabbit rudely ran.'

• To count the R's regardless of case:

select dbo.fnCount_StringInText(@text, N'R', null) as Result

• To count only the capital R's:

select dbo.fnCount_StringInText(@text, N'R', 1) as Result

ML