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.