22,206
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2012-11-23 13:35:10
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
go
create table [test]([col] varchar(48))
insert [test]
select 'BEN915' union all
select 'BEN915B78809B79576BG6159BRD933' union all
select 'BVM768' union all
select 'BVM768' union all
select 'BLM557' union all
select 'BLM557B8M970' union all
select 'B820J0B18608B3W072BVY585' union all
select 'B886E2B9V839BU9878U75972B036E1B31P88BG3509UQ4763'
go
select
[col],
substring([col],number,6) as NewCol
from
test,master..spt_values a
where
a.type='p'
and number between 1 and len(col)
and number%6=1
/*
col NewCol
------------------------------------------------ ------------
BEN915 BEN915
BEN915B78809B79576BG6159BRD933 BEN915
BEN915B78809B79576BG6159BRD933 B78809
BEN915B78809B79576BG6159BRD933 B79576
BEN915B78809B79576BG6159BRD933 BG6159
BEN915B78809B79576BG6159BRD933 BRD933
BVM768 BVM768
BVM768 BVM768
BLM557 BLM557
BLM557B8M970 BLM557
BLM557B8M970 B8M970
B820J0B18608B3W072BVY585 B820J0
B820J0B18608B3W072BVY585 B18608
B820J0B18608B3W072BVY585 B3W072
B820J0B18608B3W072BVY585 BVY585
B886E2B9V839BU9878U75972B036E1B31P88BG3509UQ4763 B886E2
B886E2B9V839BU9878U75972B036E1B31P88BG3509UQ4763 B9V839
B886E2B9V839BU9878U75972B036E1B31P88BG3509UQ4763 BU9878
B886E2B9V839BU9878U75972B036E1B31P88BG3509UQ4763 U75972
B886E2B9V839BU9878U75972B036E1B31P88BG3509UQ4763 B036E1
B886E2B9V839BU9878U75972B036E1B31P88BG3509UQ4763 B31P88
B886E2B9V839BU9878U75972B036E1B31P88BG3509UQ4763 BG3509
B886E2B9V839BU9878U75972B036E1B31P88BG3509UQ4763 UQ4763
(23 行受影响)
*/
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split int
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
WHILE len(@s)>@split
BEGIN
INSERT @re VALUES(substring(@s,1,@split))
SET @s=STUFF(@s,1,@split,'')
END
INSERT @re VALUES(@s)
RETURN
END
GO
;with tb(付款单位)
as(
select 'BEN915' union all
select 'BEN915B78809B79576BG6159BRD933' union all
select 'BVM768' union all
select 'BVM768' union all
select 'BLM557' union all
select 'BLM557B8M970' union all
select 'B820J0B18608B3W072BVY585' union all
select 'B886E2B9V839BU9878U75972B036E1B31P88BG3509UQ4763'
)
select * from dbo.f_splitSTR((select 付款单位+'' from tb for xml path('')),6)
----------------------------------------------------------------
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2012-11-23 13:35:10
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
go
create table [test]([col] varchar(48))
insert [test]
select 'BEN915' union all
select 'BEN915B78809B79576BG6159BRD933' union all
select 'BVM768' union all
select 'BVM768' union all
select 'BLM557' union all
select 'BLM557B8M970' union all
select 'B820J0B18608B3W072BVY585' union all
select 'B886E2B9V839BU9878U75972B036E1B31P88BG3509UQ4763'
go
;with t
as(
select
[col],
substring([col],number,6) as NewCol
from
test,master..spt_values a
where
a.type='p'
and number between 1 and len(col)
and number%6=1
)
select
distinct *
from t
where NewCol <>''
order by col
/*
col NewCol
------------------------------------------------ ------------
B820J0B18608B3W072BVY585 B18608
B820J0B18608B3W072BVY585 B3W072
B820J0B18608B3W072BVY585 B820J0
B820J0B18608B3W072BVY585 BVY585
B886E2B9V839BU9878U75972B036E1B31P88BG3509UQ4763 B036E1
B886E2B9V839BU9878U75972B036E1B31P88BG3509UQ4763 B31P88
B886E2B9V839BU9878U75972B036E1B31P88BG3509UQ4763 B886E2
B886E2B9V839BU9878U75972B036E1B31P88BG3509UQ4763 B9V839
B886E2B9V839BU9878U75972B036E1B31P88BG3509UQ4763 BG3509
B886E2B9V839BU9878U75972B036E1B31P88BG3509UQ4763 BU9878
B886E2B9V839BU9878U75972B036E1B31P88BG3509UQ4763 U75972
B886E2B9V839BU9878U75972B036E1B31P88BG3509UQ4763 UQ4763
BEN915 BEN915
BEN915B78809B79576BG6159BRD933 B78809
BEN915B78809B79576BG6159BRD933 B79576
BEN915B78809B79576BG6159BRD933 BEN915
BEN915B78809B79576BG6159BRD933 BG6159
BEN915B78809B79576BG6159BRD933 BRD933
BLM557 BLM557
BLM557B8M970 B8M970
BLM557B8M970 BLM557
BVM768 BVM768
(22 行受影响)
*/
修改了一下
----------------------------------------------------------------
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2012-11-23 13:35:10
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
go
create table [test]([col] varchar(48))
insert [test]
select 'BEN915' union all
select 'BEN915B78809B79576BG6159BRD933' union all
select 'BVM768' union all
select 'BVM768' union all
select 'BLM557' union all
select 'BLM557B8M970' union all
select 'B820J0B18608B3W072BVY585' union all
select 'B886E2B9V839BU9878U75972B036E1B31P88BG3509UQ4763'
go
declare @len int
select @len=max(LEN(col)) from test
--select @len
;with t
as(
select
px=row_number()over(partition by [col] order by (select 1)),
[col],
substring([col],number,6) as NewCol
from
test,master..spt_values a
where
a.type='p'
and number between 1 and @len
and number%6=1
)
select
*
from t
where NewCol <>''
/*
px col NewCol
1 B820J0B18608B3W072BVY585 B820J0
2 B820J0B18608B3W072BVY585 B18608
3 B820J0B18608B3W072BVY585 B3W072
4 B820J0B18608B3W072BVY585 BVY585
1 B886E2B9V839BU9878U75972B036E1B31P88BG3509UQ4763 B886E2
2 B886E2B9V839BU9878U75972B036E1B31P88BG3509UQ4763 B9V839
3 B886E2B9V839BU9878U75972B036E1B31P88BG3509UQ4763 BU9878
4 B886E2B9V839BU9878U75972B036E1B31P88BG3509UQ4763 U75972
5 B886E2B9V839BU9878U75972B036E1B31P88BG3509UQ4763 B036E1
6 B886E2B9V839BU9878U75972B036E1B31P88BG3509UQ4763 B31P88
7 B886E2B9V839BU9878U75972B036E1B31P88BG3509UQ4763 BG3509
8 B886E2B9V839BU9878U75972B036E1B31P88BG3509UQ4763 UQ4763
1 BEN915 BEN915
1 BEN915B78809B79576BG6159BRD933 BEN915
2 BEN915B78809B79576BG6159BRD933 B78809
3 BEN915B78809B79576BG6159BRD933 B79576
4 BEN915B78809B79576BG6159BRD933 BG6159
5 BEN915B78809B79576BG6159BRD933 BRD933
1 BLM557 BLM557
1 BLM557B8M970 BLM557
2 BLM557B8M970 B8M970
1 BVM768 BVM768
9 BVM768 BVM768
*/