sql解析有规律字符串为表结构



解析字符串为：'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，请分享。