[MSSQL]固定長度新增分隔符函式

廈門德仔發表於2012-02-16
SQL函式
 
-- =============================================
-- 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)
*/

相關文章