sql and csharp: Split Function

T-SQL:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114

declare @int int,@prov int,@city int,@str nvarchar(500) set @str='天河麗特青春：中國廣東省廣州市天河區天河路623號天河娛樂廣場麗特青春百貨一樓,塗聚文' select @int=charindex('：',@str) select @prov=charindex('省',@str) select @city=charindex('市',@str) select substring(@str,0,@int) select substring(@str,@int+1,@prov-(@int)) select substring(@str,@int+1,@city-(@int))   declare @int int,@prov int,@city int,@str nvarchar(500),@branch varchar(20) select @branch='HDF' select @int=charindex('：',ShopAddress)  from  Intranet.dbo.LC where BranchNO=@branch select @prov=charindex('省',ShopAddress)  from  Intranet.dbo.LC where BranchNO=@branch select @city=charindex('市',ShopAddress)  from  Intranet.dbo.LC where BranchNO=@branch select substring(ShopAddress,0,@int) from  Intranet.dbo.LC where BranchNO=@branch select substring(ShopAddress,@int+1,@prov-(@int)) from  Intranet.dbo.LC where BranchNO=@branch select substring(ShopAddress,@int+1,@city-(@int)) from  Intranet.dbo.LC where BranchNO=@branch   select substring(ShopAddress,0,charindex('：',ShopAddress)) from  Intranet.dbo.LC   ---中國國內分店名稱 select BranchNO+'--'+CompanyName+substring(ShopAddress,0,charindex('市',ShopAddress)+1) from  Intranet.dbo.LC   select BranchNO,CompanyName,ShopAddress  substring(@str,@int+1,@city-(@int)) from Intranet.dbo.LC   drop function [dbo].getVipExamBranchName go --- create function [dbo].getVipExamBranchName (     @branch varchar(20),     @key nvarchar(20),     @citykey nvarchar(20) ) RETURNS NVARCHAR(200) AS BEGIN declare @int int,@prov int,@city int,@str nvarchar(500),@branchcode nvarchar(20),@re NVARCHAR(100),@cityname nvarchar(500) select @int=charindex(@key,ShopAddress)  from  Intranet.dbo.LC where BranchNO=@branch--'：' --select @prov=charindex('省',ShopAddress)  from  Intranet.dbo.LC where BranchNO=@branch select @city=charindex(@citykey,ShopAddress)  from  Intranet.dbo.LC where BranchNO=@branch--'市' select @branchcode=substring(ShopAddress,0,@int) from  Intranet.dbo.LC where BranchNO=@branch --select substring(ShopAddress,@int+1,@prov-(@int)) from  Intranet.dbo.LC where BranchNO=@branch if(@city>@int)<br>select @cityname=substring(ShopAddress,@int+1,@city-(@int)) from  Intranet.dbo.LC where BranchNO=@branch select @re=@branchcode+'--'+@cityname RETURN @re end GO select [dbo].getVipExamBranchName ('HDF','：','市')     ---函數 CREATE FUNCTION [dbo].[func_Split]     (       @DelimitedString    varchar(8000),     @Delimiter              varchar(100)     ) RETURNS @tblArray TABLE     (     ElementID   int IDENTITY(1,1),  -- Array index     Element     varchar(1000)               -- Array element contents     ) AS BEGIN        -- Local Variable Declarations     -- ---------------------------     DECLARE @Index      smallint,                     @Start      smallint,                     @DelSize    smallint        SET @DelSize = LEN(@Delimiter)        -- Loop through source string and add elements to destination table array     -- ----------------------------------------------------------------------     WHILE LEN(@DelimitedString) > 0     BEGIN            SET @Index = CHARINDEX(@Delimiter, @DelimitedString)            IF @Index = 0             BEGIN                    INSERT INTO                     @tblArray                     (Element)                 VALUES                     (LTRIM(RTRIM(@DelimitedString)))                    BREAK             END         ELSE             BEGIN                    INSERT INTO                     @tblArray                     (Element)                 VALUES                     (LTRIM(RTRIM(SUBSTRING(@DelimitedString, 1,@Index - 1))))                    SET @Start = @Index + @DelSize                 SET @DelimitedString = SUBSTRING(@DelimitedString, @Start , LEN(@DelimitedString) - @Start + 1)                END     END        RETURN END   --測試 DECLARE @SQLStr varchar(100) SELECT @SQLStr = 'Mickey Mouse, Goofy, Donald Duck, Pluto, Minnie Mouse'    SELECT *  FROM    dbo.func_split(@SQLStr, ',')

 csharp:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

/// <summary>        /// 分割字符串        /// 塗聚文        ///        /// </summary>        /// <param name="str"></param>        /// <param name="key"></param>        /// <returns></returns>        public ArrayList getSplit(string str,char key)        {            ArrayList alist = new ArrayList();              string[] sArray = str.Split(key);              foreach (string i in sArray)            {                                 alist.Add(i.ToString());            }            return alist;        }        /// <summary>        /// 正則表達式分割字符串        /// </summary>        /// <param name="str"></param>        /// <param name="key"></param>        /// <returns></returns>        public ArrayList getRegexSplit(string str, string key)        {            ArrayList alist = new ArrayList();            string[] resultString = Regex.Split(str, key, RegexOptions.IgnoreCase);            foreach(string i in resultString)            {                alist.Add(i.ToString());            }            return alist;        }