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