有没有人可以帮助我们使用SQL Server 2008用户定义的函数在任意字符串或数字之间添加空格?
例如:将数字12345转换为1 2 3 4 5
发布于 2017-10-04 23:02:06
使用ngrams8k,您可以创建以下内容:
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,'');使用的
select newString from dbo.itvf_padtext('558899');返回
5 5 8 8 9 9针对表的
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);返回
someString newString
--------------- ------------
abc a b c
567 5 6 7
pdqxxx p d q x x x Update显示不想使用标量的原因(在OP注释中推荐)
下面是比较标量udf和iTVF的性能测试;请注意我在代码中的注释。我将发布的是快2-5倍的。
-- 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结果
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.https://stackoverflow.com/questions/46555823
复制相似问题