固定长度添加分隔符函数代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】固定长度添加分隔符函数

作者:maco_wang / 发布于2012/2/8/ 529

<div>-- =============================================
-- Author: maco_wang
-- Create date: 2012-01-08
-- Description:固定长度添加分隔符函数
-- =============================================</div>
<div>create function AddSplitSign
(
@colname varchar(8000),
 @len int --固定长度
)
returns varchar(8000)
as
begin
 declare @j int set @j=ceiling(len(@colname)/(@len*1.0))-1
 while(@j>0)
 begin
 set @colname=stuff(@colname,@len*@j+1,0,'-')
 set @j=@j-1
 end
 return @colname
end</div>
<div>
declare @T table (col varchar(36))
insert into @T
select 'AEB3166C-B7C6-4C5D-BF0B-F452626EF266' union all
select '79EA0FFB-7BF5-4F49-BEC4-E0C6F5ADC3AB' union all
select '5B7B59AF-AF2A-4CB1-8CCC-27AB75A6A845' union all
select 'B829A0A2-6B08-42F0-86B3-69D276664579' union all
select '0F0E1FCC-3256-4364-9FE8-80B180A9C6FB' union all
select '5C3D3CED-C650-4E8D-AF16-0870D0500581' union all
select '9B2C0A05-46CE-4CFE-85FB-E6C460C5C771' union all
select '79F6DE39-BEC5-4D55-8A67-021FD8C72280' union all
select 'E163704D-0248-4AD1-B4E5-FDC374B60304' union all
select 'D8A1B062-CC90-41F9-A359-108263C7CF47' union all
select '9A2227ED-C2D8-4BDD-B2C9-182485E659C9'</div>
<div>--将表中-去掉，然后每4个一分割
select col=dbo.AddSplitSign(replace(col,'-',''),4) from @T</div>
<div>--查看结果
/*
col
---------------------------------------
AEB3-166C-B7C6-4C5D-BF0B-F452-626E-F266
79EA-0FFB-7BF5-4F49-BEC4-E0C6-F5AD-C3AB
5B7B-59AF-AF2A-4CB1-8CCC-27AB-75A6-A845
B829-A0A2-6B08-42F0-86B3-69D2-7666-4579
0F0E-1FCC-3256-4364-9FE8-80B1-80A9-C6FB
5C3D-3CED-C650-4E8D-AF16-0870-D050-0581
9B2C-0A05-46CE-4CFE-85FB-E6C4-60C5-C771
79F6-DE39-BEC5-4D55-8A67-021F-D8C7-2280
E163-704D-0248-4AD1-B4E5-FDC3-74B6-0304
D8A1-B062-CC90-41F9-A359-1082-63C7-CF47
9A22-27ED-C2D8-4BDD-B2C9-1824-85E6-59C9</div>
<div>(11 row(s) affected)
*/
</div>

试试其它关键字

　添加分隔符　

同语言下

可能有用的

maco_wang贡献的其它代码(36)