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

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.