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)
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, ',')

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

        }