各位高手,如何从一个字段当中取出固定字符?在线等,谢谢

anwnvliujing 2012-11-23 01:31:33
各位高手,有一张表有“付款单位”这个字段,字段的长度都是6的倍数,如何生成一张新表,把一个字段当中每6位截取成一条新的记录,比如倒数第三条记录变成BLM557与B8M970两条记录?谢谢。
付款单位
BEN915
BEN915B78809B79576BG6159BRD933
BVM768
BVM768
BLM557
BLM557B8M970
B820J0B18608B3W072BVY585
B886E2B9V839BU9878U75972B036E1B31P88BG3509UQ4763
...全文
278 12 打赏 收藏 转发到动态 举报
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
vliujing 2012-12-28
  • 打赏
  • 举报
回复
您好,请参考以下提示来解决您的问题。 您的权限无法使用此功能,请联系相关人员,获得使用此功能的权限,才可以使用。 如果您想换一个账号来使用本功能,请点击后面链接注销:注销 为什么不能结帖给分呢??
anwnvliujing 2012-11-23
  • 打赏
  • 举报
回复
威武!!谢谢!!
  • 打赏
  • 举报
回复
引用 8 楼 anwnvliujing 的回复:
谢谢高手的回复,我的这个表名为fp,里面还有其他的字段。 我在SQL Server里面执行完自定义函数后又执行 select f_splitSTR(付款单位,6) from fp为什么执行不出来?
不需要自定义函数啊 直接我楼上的方法 效率上也有优势
anwnvliujing 2012-11-23
  • 打赏
  • 举报
回复
谢谢版主大人,按照最后一个方法解决了,非常感谢!!!
anwnvliujing 2012-11-23
  • 打赏
  • 举报
回复
谢谢高手的回复,我的这个表名为fp,里面还有其他的字段。 我在SQL Server里面执行完自定义函数后又执行 select f_splitSTR(付款单位,6) from fp为什么执行不出来?
  • 打赏
  • 举报
回复

----------------------------------------------------------------
-- 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
*/


anwnvliujing 2012-11-23
  • 打赏
  • 举报
回复
链接中的问题跟我的问题不一样啊。
anwnvliujing 2012-11-23
  • 打赏
  • 举报
回复
谢谢楼上的回复。
Mr_Nice 2012-11-23
  • 打赏
  • 举报
回复
貌似只能游标拆字符串了。 参考http://bbs.csdn.net/topics/370020976

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧