sql and csharp: Split Function

T-SQL:

 

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
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:

 /// <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;
        }