解析字符串为:'101:1011111;202:20;303:303;404:40111;505:505;606:60212121;101:1011111'
此处提供三种方式。第二和第三种为转载其他博客代码,第一种为第二种的精简版本。通过测试IO数据,效率分别降低。第一种速度最快(环境:sql2014/5000个有规律字符)
第一/二种:使用sql解析xml的方式,将字符串里面的特殊有规律字符通过replace替换成xml节点。
第三种:使用sql的substring截取需要的内容。通过charindex查找和得到substring的索引,开始位置和长度
SET STATISTICS IO ON
SET STATISTICS TIME ON
/*查询语句开始*/
--declare str , save parameter
DECLARE @Str VARCHAR(MAX)
SET @Str='101:1011111;202:20;303:303;404:40111;505:505;606:60212121;101:1011111'
DECLARE @ItemMessage XML
SET @ItemMessage='<item xmlns=''1''><st><op>'+REPLACE(REPLACE(@Str,';','</it></st><st><op>'),':','</op><it>')+'</it></st></item>' -- set value
;with XMLNAMESPACES(default '1') --查询指定xmlns节点,如果有多个外层节点时使用(item节点)
SELECT T.a.value('(op/text())[1]','INT') as ItemNumber, --取节点op下的第一个节点的值(如果有多个op节点,按索引取值,索引从1开始)
T.a.value('(it/text())[1]','NVARCHAR(300)') as ItemDescription
FROM @ItemMessage.nodes('/item/st') as T(a)
/*查询语句结束*/
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
----------------------------------------------------------------------------------------------------
--和第一种一样,定义了变量表,相当于定义查询结果的表结构
SET STATISTICS IO ON
SET STATISTICS TIME ON
/*查询语句开始*/
DECLARE @Str VARCHAR(MAX)
SET @Str='101:1011111;202:20;303:303;404:40111;505:505;606:60212121;101:1011111'
DECLARE @Msg_c XML
DECLARE @ItemTable TABLE(ItemNumber varchar(100),ItemDescription NVARCHAR(300))
SET @Msg_c = '<item><st><op>'+REPLACE(REPLACE(@Str,';','</it></st><st><op>'),':','</op><it>')+'</it></st></item>'
INSERT INTO @ItemTable
(
ItemNumber,
ItemDescription
)
SELECT T.c.value('(op/text())[1]','NVARCHAR(100)'),
T.c.value('(it/text())[1]','NVARCHAR(100)')
FROM @Msg_c.nodes('/item/st') AS T(c)
SELECT ItemNumber,
ItemDescription
FROM @ItemTable
/*查询语句结束*/
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
----------------------------------------------------------------------------------------------------
--原理:通过substring函数,递增index递增确定charindex函数的开始index。
if exists(select 1 from sysobjects where id=object_id('func_splitstring') and objectproperty(id,'IsInlineFunction')=0)
begin
drop function func_splitstring
end
else
begin
print 'bb'
end
create function func_splitstring
(@str nvarchar(max),@splitone varchar(1),@splitwo varchar(1))
returns @t Table (c1 varchar(100),c2 varchar(100))
as
begin
declare @i int --字符串截取opportunity整体数据长度。例如:300014:1001
declare @s int --@splitone字符串截取opportunity开始位置
declare @strt varchar(max)--保存opportunity数据。
declare @a int --获取opportunity id的长度
declare @b int --获取opportunity item id的长度
set @i=1
set @s=1
while(@i>0)
begin
set @i=charindex(@splitone,@str,@s)
--@i 大于0是获取除最后一条;隔开的数据。反知是获取最后一条数据
if(@i>0)
begin
set @strt=substring(@str,@s,@i-@s) --获取单条opportunity数据。例如:1000000001:1002
end
else begin
--当@i 小于0的时候,没有找到@splitone , 证明是最后一组(了解charindex函数即可理解)
set @strt=substring(@str,@s,len(@str)-@s+1) --获取单条opportunity数据。例如:1000000001:1002
end
set @a=charindex(@splitwo,@strt)
set @b=len(@strt) - @a --获取item的长度
insert @t(c1,c2) values(substring(@strt,1,@a-1),substring(@strt,@a+1,@b))
set @s = @i + 1
end
return
end
SET STATISTICS IO ON
SET STATISTICS TIME ON
/*查询语句开始*/
--调用
select * from func_splitstring('101:1011111;202:20;303:303;404:40111;505:505;606:60212121;101:1011111', ';',':')
/*查询语句结束*/
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
我在想第一种能不能再简化一些,提高执行效率,降低IO。如有好的idea,请分享。