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
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
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.