SQL Server 2008 user defined function to add spaces between each digit

Question

Can anyone please help with a SQL Server 2008 user-defined function to add spaces between any string or number?

Ex: to convert a number 12345 to 1 2 3 4 5

Answer 1

With ngrams8k you could create this:

create function dbo.itvf_padtext(@string varchar(8000))
returns table with schemabinding as return
select newString = 
stuff((select ' '+token
from dbo.ngrams8k(@string,1)
order by position
for xml path('')),1,1,'');

To use

select newString from dbo.itvf_padtext('558899');

returns

5 5 8 8 9 9

Against a table

declare @sometable table (someid int identity, somestring varchar(100));
insert @sometable(somestring) values ('abc'), ('567'), ('pdqxxx');

select someString, newString 
from @sometable t
cross apply dbo.itvf_padtext(t.somestring);

returns

someString      newString
--------------- ------------  
abc             a b c           
567             5 6 7           
pdqxxx          p d q x x x     

Update - showing why you don't want to use a scalar udf (as recommended in the OP comments)

Below is a performance test comparing the scalar udf to the iTVF; note my comments in the code. What I posted will be I posted is 2-5 times faster.

-- sample data
if object_id('tempdb..#strings') is not null drop table #strings;
select top (10000)  -- 10 rows, 5 to 16 characters
  string = left(cast(newid() as varchar(36)), abs(checksum(newid())%17)+5)
into #strings
from sys.all_columns a, sys.all_columns b;
go
-- note that the scalar udf will only run with a serial execution plan
print 'scalar'+char(13)+char(10)+replicate('-',50)
go
  declare @st datetime = getdate(), @x varchar(36);
  select @x = dbo.udf_PutSpacesBetweenChars(t.string)
  from #strings t;
print datediff(ms,@st,getdate())
go 3

print 'ngrams serial'+char(13)+char(10)+replicate('-',50)
go
  declare @st datetime = getdate(), @x varchar(36);
  select @x = newstring 
  from #strings t
    cross apply dbo.itvf_padtext(t.string)
    option (maxdop 1); --force a serial plan
print datediff(ms,@st,getdate());
go 3

print 'ngrams parallel'+char(13)+char(10)+replicate('-',50)
go
  declare @st datetime = getdate(), @x varchar(36);
  select @x = newstring 
  from #strings t
    cross apply dbo.itvf_padtext(t.string)
  option (recompile, querytraceon 8649); -- force a parallel plan
print datediff(ms,@st,getdate())
go 3

Results

scalar
--------------------------------------------------
Beginning execution loop
116
114
120
Batch execution completed 3 times.

ngrams serial
--------------------------------------------------
Beginning execution loop
50
50
50
Batch execution completed 3 times.

ngrams parallel
--------------------------------------------------
Beginning execution loop
23
24
20
Batch execution completed 3 times.