【叶子函数分享五十八】固定长度添加分隔符函数

-- ============================================= -- Author: maco_wang -- Create date: 2012-01-08 -- Description: 固定长度添加分隔符函数 -- ============================================= 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 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' --将表中-去掉，然后每4个一分割 select col=dbo.AddSplitSign(replace(col,'-',''),4) from @T --查看结果 /* 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 (11 row(s) affected) */

上面的函数有个问题，分隔符不能自定义，扩展一下

--固定长度添加分隔符函数 create function [AddSplitSignV2.0] ( @colname varchar(8000), @len int, --固定长度 @sign varchar(10) ) 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,@sign) set @j=@j-1 end return @colname end 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' --将表中"-"去掉，然后每间隔4个字符添加一个":"分割符 select col=dbo.[AddSplitSignV2.0](replace(col,'-',''),4,':') from @T --查看结果 /* 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 (11 row(s) affected) */