[MSSQL]固定长度添加分隔符函数

最新推荐文章于 2022-11-24 08:56:33 发布

厦门德仔

最新推荐文章于 2022-11-24 08:56:33 发布

阅读量1.6k

点赞数

分类专栏： MSSQL 文章标签： c insert function table date

MSSQL 专栏收录该内容

293 篇文章 11 订阅 ¥99.90 ¥299.90

订阅专栏

超级会员免费看

本文介绍了如何使用SQL函数AddSplitSign和AddSplitSignV2.0来对固定长度的字符串添加分隔符，例如将GUID字符串每4个字符进行分隔，方便阅读和处理。示例中展示了去除原始字符串中的'-'字符，并用':'或'-'作为新的分隔符。

摘要由CSDN通过智能技术生成

-- =============================================
-- 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)

*/