snaps & snippets

See "The First One"

RSS Feed
MVP – Windows Server System – SQL Server

Current posts | Archives | Links | Popular | Technorati | Company site

Snaps | Snippets

Thursday, February 16, 2006

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

Friday, February 10, 2006

Fun with strings

Let's imagine there's a string of alpha-numeric and numeric characters and we need to delimit the string in order to separate both types of characters, e.g. to prepare values for normalization. The following scalar function (minus a few bugs) inserts a parameterised delimiter (a single space as the default) between individual series of apha-numeric and numeric data:
create function dbo.fnGet_DelimitedString_byNumChar
 (
 @inStr  nvarchar(max)
 ,@delimiter nvarchar(16)  = null
 )
returns nvarchar(max)
as
begin
 declare @result   nvarchar(max)
 declare @curChar  int
 declare @numLoops  int
 declare @lookForNum  bit
 declare @lookForPattern  nvarchar(16)
 declare @lookForNumber  nvarchar(16)
 declare @lookForNotNumber nvarchar(16)

 if (@delimiter is null)
  begin
   set @delimiter = N' '
  end

 set @lookForNumber = N'%[0-9]%'
 set @lookForNotNumber = N'%[^0-9]%'

 set @numLoops = 1

 if (patindex(@lookForNumber, left(@inStr, 1)) = 0)
  begin
   set @lookForNum = 0
   set @lookForPattern = @lookForNotNumber
  end
 else
  begin
   set @lookForNum = 1
   set @lookForPattern = @lookForNumber
  end

 set @curChar = patindex(@lookForPattern, @inStr)
 set @result = substring(@inStr, 0, @curChar)

 while (@curChar != 0)
  begin
   if (@lookForNum = 1)
    begin
     set @inStr = substring(@inStr, @curChar, datalength(@inStr))

     set @result
       = @result
       + case
        when right(@result, datalength(@delimiter) / 2) = @delimiter
         or left(@inStr, datalength(@delimiter) / 2) = @delimiter
         or @numLoops = 1
         then N''
        else @delimiter
        end
       + substring(@inStr, 0, patindex(@lookForNotNumber, @inStr))

     set @lookForNum = 0
     set @lookForPattern = @lookForNotNumber
    end
   else
    begin
     set @inStr = substring(@inStr, @curChar, datalength(@inStr))

     set @result
       = @result
       + case
        when right(@result, datalength(@delimiter) / 2) = @delimiter
         or left(@inStr, datalength(@delimiter) / 2) = @delimiter
         or @numLoops = 1
         then N''
        else @delimiter
        end
       + substring(@inStr, 0, patindex(@lookForNumber, @inStr))

     set @lookForNum = 1
     set @lookForPattern = @lookForNumber
    end

   set @numLoops = @numLoops + 1
   set @curChar = patindex(@lookForPattern, @inStr)
  end

 set @result = @result + @inStr

 return @result
end
go
A few examples of use:
select dbo.fnGet_DelimitedString_byNumChar('512btc', '-')
union all
select dbo.fnGet_DelimitedString_byNumChar('FX788H', null)
union all
select dbo.fnGet_DelimitedString_byNumChar('S XX12', null)
union all
select dbo.fnGet_DelimitedString_byNumChar('S XX12OH', null)
Silly, isn't it...? ;) But add this function by Dejan Sarka to the picture, and it makes a bit more sense. ML p.s. Thanks to Razvan Socol for catching the bugs which have now been crushed.