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.