sql server函数实现把分隔字符串转成表格

来源：互联网发布：小米笔记本l 知乎编辑：程序博客网时间：2024/04/29 14:43

CREATE FUNCTION [dbo].[GetTableFromString] (-- Add the parameters for the function here@sourceStr nvarchar(max) )RETURNS @Table_NameList table ( Name Varchar(max))  -- 建立表变量 ASBEGINDeclare @Index_Param int   /*参数 记录分隔符的位置*/Declare @NeedParse varchar(max) /*参数 没有处理的字符串*/if(@sourceStr is NULL OR Ltrim(Rtrim(@sourceStr))='')returnSelect  @Index_Param=CharIndex(',', @sourceStr)if (@Index_Param=0)begin        /*一个名字组成*/insert into @Table_NameList (Name) values(@sourceStr)endelse   /*存在多个名字*/beginset @NeedParse =@sourceStrwhile (CharIndex(',', @NeedParse)>0)begininsert into @Table_NameList (Name) values(SubString(@NeedParse,1,CharIndex(',',@NeedParse)-1))set @NeedParse =SubString(@NeedParse,CharIndex(',', @NeedParse)+1,len(@NeedParse)-CharIndex(',', @NeedParse))endif(len(@NeedParse)>0)insert into @Table_NameList (Name) values(@NeedParse)endreturnEND

CREATE FUNCTION [dbo].[GetTableFromStringBySplit] (-- Add the parameters for the function here@sourceStr nvarchar(max),@splitStr nvarchar(max)=',' )RETURNS @Table_NameList table ( Name Varchar(max))  -- 建立表变量 ASBEGINDeclare @Index_Param int   /*参数 记录分隔符的位置*/Declare @NeedParse varchar(max) /*参数 没有处理的字符串*/declare @splitLength intset @splitLength = len(@splitStr)if(@sourceStr is NULL OR Ltrim(Rtrim(@sourceStr))='')returnSelect  @Index_Param=CharIndex(@splitStr, @sourceStr)if (@Index_Param=0)begin        /*一个名字组成*/insert into @Table_NameList (Name) values(@sourceStr)endelse     /*存在多个名字*/beginset @NeedParse =@sourceStrwhile (CharIndex(@splitStr, @NeedParse)>0)begininsert into @Table_NameList (Name) values(SubString(@NeedParse,1,CharIndex(@splitStr,@NeedParse)-1))set @NeedParse =SubString(@NeedParse,CharIndex(@splitStr, @NeedParse)+@splitLength,len(@NeedParse)-CharIndex(@splitStr, @NeedParse))endif(len(@NeedParse)>0)insert into @Table_NameList (Name) values(@NeedParse)endreturnEND

CREATE FUNCTION [dbo].[GetTableFromStringBySplitAndChildSplit] (-- Add the parameters for the function here@sourceStr nvarchar(max),@splitStr nvarchar(max)=',',@valueSplitStr nvarchar(max)=':')RETURNS @Table_NameList table (ID int, Name nVarchar(max))  -- 建立表变量 ASBEGINDeclare @Index_Param int   /*参数 记录分隔符的位置*/Declare @NeedParse nvarchar(max) /*参数 没有处理的字符串*/declare @splitLength intdeclare @sourceSplitedStr nvarchar(max)declare @childSplitIndex intset @splitLength = len(@splitStr)if(@sourceStr is NULL OR Ltrim(Rtrim(@sourceStr))='')returnSelect  @Index_Param=CharIndex(@splitStr, @sourceStr)if (@Index_Param=0)begin        /*一个名字组成*/set @childSplitIndex = CharIndex(@valueSplitStr,@sourceStr)set @sourceSplitedStr = @sourceStr--insert into @Table_NameList (Name) values(@sourceStr)insert into @Table_NameList (ID,Name) values(SubString(@sourceSplitedStr,1,@childSplitIndex-1),SubString(@sourceSplitedStr,@childSplitIndex + 1,len(@sourceSplitedStr)))endelse     /*存在多个名字*/beginset @NeedParse =@sourceStrwhile (CharIndex(@splitStr, @NeedParse)>0)beginset @childSplitIndex = CharIndex(@valueSplitStr,@NeedParse)set @sourceSplitedStr = SubString(@NeedParse,1,CharIndex(@splitStr,@NeedParse)-1)--insert into @Table_NameList (Name) values(SubString(@NeedParse,1,CharIndex(@splitStr,@NeedParse)-1))insert into @Table_NameList (ID,Name) values(SubString(@sourceSplitedStr,1,@childSplitIndex-1),SubString(@sourceSplitedStr,@childSplitIndex + 1,len(@sourceSplitedStr)))set @NeedParse =SubString(@NeedParse,CharIndex(@splitStr, @NeedParse)+@splitLength,len(@NeedParse)-CharIndex(@splitStr, @NeedParse))endif(len(@NeedParse) > 0)beginset @childSplitIndex = CharIndex(@valueSplitStr,@NeedParse)set @sourceSplitedStr = @NeedParse--insert into @Table_NameList (Name) values(@NeedParse)insert into @Table_NameList (ID,Name) values(SubString(@sourceSplitedStr,1,@childSplitIndex-1),SubString(@sourceSplitedStr,@childSplitIndex + 1,len(@sourceSplitedStr)))endendreturnEND

select * from dbo.[GetTableFromStringBySplitAndChildSplit]('1,a;2,b;3,c',';',',')

0 0