62,074
社区成员
发帖
与我相关
我的任务
分享
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <zhoulizuo>
-- Create date: <2008-9-12>
-- Description: <功能:实现split功能的函数:删除时,传递的参数是多个要删除的主键 >
-- =============================================
alter function [dbo].[PrimaryKey_split]
(
@inputstr varchar(8000),
@seprator nvarchar(10)
)
returns @temp table (Pri_Key nvarchar(200),number nvarchar(20))
as
begin
declare @num varchar(1000)
declare @i int
declare @EveryKeyLen int--每个主键的长度
--每个主键的长度
declare @EveryKeyValue nvarchar(200)
declare @str varchar(1000)
declare @size int
set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)
while @i >= 1
begin
set @EveryKeyValue =left(@inputstr, @i -1)
if(len(@EveryKeyValue)>0)
set @str=@EveryKeyValue
set @num=''
set @size=0
while len(@str)!=@size
begin
set @size=len(@str)
set @num=@num+substring(@str,patindex('%[0-9]%',@str),1)
set @str=right(@str,len(@str)-patindex('%[0-9]%',@str))
end
set @EveryKeyValue=replace(@EveryKeyValue,@num,'')
insert @temp(Pri_Key,number) values(@EveryKeyValue,@num)
set @inputstr = substring(@inputstr,@i+1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end
if @inputstr <> '\' and len(@inputstr)>0
set @str=@inputstr
set @num=''
set @size=0
while len(@str)!=@size
begin
set @size=len(@str)
set @num=@num+substring(@str,patindex('%[0-9]%',@str),1)
set @str=right(@str,len(@str)-patindex('%[0-9]%',@str))
end
set @inputstr=replace(@inputstr,@num,'')
insert @temp(Pri_Key,number) values(@inputstr,@num)
return
end
--调用
--select * from PrimaryKey_split ('酢醬1份豇豆2份鑲肉3份牛腱4份肴肉5份酢醬1份豇豆2份鑲肉3份牛腱4份肴肉5份酢醬1份豇豆2份鑲肉3份牛腱4份肴肉5份酢醬1份豇豆2份鑲肉3份牛腱4份肴肉5份酢醬1份豇豆2份鑲肉3份牛腱4份肴肉5份酢醬1份豇豆2份鑲肉3份牛腱4份肴肉5份酢醬1份豇豆2份鑲肉3份牛腱4份肴肉5份','份')
public string GetStr(string a)
{
a = a.Replace("份", "");
string temp = "";
Dictionary<string, int> dic = new Dictionary<string, int>();
for (int i = 0; i < a.Length; i++)
{
string b = a.Substring(i, 1);
if (!IsNum(b))
{
temp += b;
}
else
{
if (dic.ContainsKey(temp))
{
int value = (int)dic[temp];
dic[temp] = value + Int32.Parse(b);
}
else
{
dic.Add(temp, Int32.Parse(b));
}
temp = "";
}
}
string re = "";
foreach (object var in dic.Keys)
{
re += var.ToString() + ":" + dic[var.ToString()] + "份";
}
return re;
}
public bool IsNum(string str)
{
int a = 0;
try
{
a = Convert.ToInt32(str);
return true;
}
catch (Exception)
{
return false;
}
}