22,210
社区成员
发帖
与我相关
我的任务
分享
If not object_id('[test_tb01]') is null
begin
Drop table [test_tb01];
end
Go
-----------------------------------------------------------
-----------------------------------------------------------
Create table [test_tb01]
(
id int IDENTITY(1,1) NOT NULL,
[field] nvarchar(200) COLLATE Chinese_PRC_CI_AS NOT NULL
)
Insert [test_tb01]([field])
Select N'餐饮集团/华东区管理处/中央厨房/营销部' union all
Select N'餐饮集团' union all
Select N'餐饮集团/华东区管理处' union all
Select N'餐饮集团/华东区管理处/营销部' union all
Select N'餐饮集团/华东区管理处/中央厨房/营销1部' union all
Select N'餐饮集团/华东区管理处/中央厨房/营销2部' union all
Select N'餐饮集团/华东区管理处/中央厨房/营销2部'
Go
-----------------------------------------------------------
-----------------------------------------------------------
CREATE function [dbo].[FUNCTION_AAAA](
@value nvarchar(200), --要提取的源串, 如'餐饮集团/华东区管理处/中央厨房/营销1部
@flag nvarchar(10) , --要查找的分隔符号, 如 '/'
@flag_begin_pos int , --源串中分隔符号出现的次数, 以该次数作为提取字串的起始
@flag_end_pos int --源串中分隔符号出现的次数, 以该次数作为提取字串的结束
)
returns nvarchar(200)
as
begin
declare @rtv nvarchar(200);
declare @pos1 int, @pos2 int, @num int, @pos int;
set @pos = 0 ;
set @pos1 = 0 ; --保存要返回的字串位于源串的起始位置
set @pos2 = 0 ; --保存要返回的字串位于源串的结束位置
set @num = 0 ; --保存遍历过程中分隔符合一共出现了多少次
if @value is null
begin
return null;
end
if @flag is null
begin
return null;
end
if @flag_begin_pos is null
begin
return null;
end
if @flag_end_pos is null
begin
return null;
end
if @flag_begin_pos >= @flag_end_pos
begin
return null;
end
while 1>0
begin
Set @pos = charindex(@flag,@value,@pos);
if @pos = 0
begin
break;
end
else
begin
set @num = @num + 1;
if @num=@flag_begin_pos
begin
set @pos1=@pos + 1;
end
else
begin
if @num = @flag_end_pos
begin
set @pos2 = @pos ;
break;
end
end
set @pos = @pos + 1;
end
end
if @num <> @flag_end_pos
begin
set @rtv = N'NULL' ; --没有在源串中找到合适的字串, 返回null
end
else
begin
set @rtv = SUBSTRING(@value,@pos1,@pos2-@pos1); ;
end
return @rtv;
end
GO
-----------------------------------------------------------
-----------------------------------------------------------
Select *, dbo.[FUNCTION_AAAA](field,N'/',2,3) from [test_tb01];
/*
1 餐饮集团/华东区管理处/中央厨房/营销部 中央厨房
2 餐饮集团 NULL
3 餐饮集团/华东区管理处 NULL
4 餐饮集团/华东区管理处/营销部 NULL
5 餐饮集团/华东区管理处/中央厨房/营销1部 中央厨房
6 餐饮集团/华东区管理处/中央厨房/营销2部 中央厨房
7 餐饮集团/华东区管理处/中央厨房/营销2部 中央厨房
*/
Select *, dbo.[FUNCTION_AAAA](field,N'/',1,3) from [test_tb01];
/*
1 餐饮集团/华东区管理处/中央厨房/营销部 华东区管理处/中央厨房
2 餐饮集团 NULL
3 餐饮集团/华东区管理处 NULL
4 餐饮集团/华东区管理处/营销部 NULL
5 餐饮集团/华东区管理处/中央厨房/营销1部 华东区管理处/中央厨房
6 餐饮集团/华东区管理处/中央厨房/营销2部 华东区管理处/中央厨房
7 餐饮集团/华东区管理处/中央厨房/营销2部 华东区管理处/中央厨房
*/
Select *, dbo.[FUNCTION_AAAA](field,N'/',1,2) from [test_tb01];
/*
1 餐饮集团/华东区管理处/中央厨房/营销部 华东区管理处
2 餐饮集团 NULL
3 餐饮集团/华东区管理处 NULL
4 餐饮集团/华东区管理处/营销部 华东区管理处
5 餐饮集团/华东区管理处/中央厨房/营销1部 华东区管理处
6 餐饮集团/华东区管理处/中央厨房/营销2部 华东区管理处
7 餐饮集团/华东区管理处/中央厨房/营销2部 华东区管理处
*/
--> 生成测试数据表:tb
If not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([col] nvarchar(21))
Insert [tb]
Select N'餐饮集团/华东区管理处/中央厨房/营销部' union all
Select N'餐饮集团' union all
Select N'餐饮集团/华东区管理处' union all
Select N'餐饮集团/华东区管理处/营销部' union all
Select N'餐饮集团/华东区管理处/中央厨房/营销1部' union all
Select N'餐饮集团/华东区管理处/中央厨房/营销2部' union all
Select N'餐饮集团/华东区管理处/中央厨房/营销2部'
Go
--Select * from [tb]
-->SQL查询如下:
If not object_id('[fn_str]') is null
Drop function [fn_str]
Go
Create function fn_str(@col varchar(8000))
returns varchar(1000)
as
begin
if len(@col)-len(replace(@col,'/',''))<3
set @col=''
else
begin
set @col=stuff(@col,1,charindex('/',@col),'')+'/'
set @col=left(@col,charindex('/',@col)-1)
end
return(@col)
end
go
select dbo.fn_str(COL) col from tb
/*
col
-----------------------
华东区管理处
华东区管理处
华东区管理处
华东区管理处
(7 行受影响)
*/
--> 生成测试数据表:tb
If not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([col] nvarchar(21))
Insert [tb]
Select N'餐饮集团/华东区管理处/中央厨房/营销部' union all
Select N'餐饮集团' union all
Select N'餐饮集团/华东区管理处' union all
Select N'餐饮集团/华东区管理处/营销部' union all
Select N'餐饮集团/华东区管理处/中央厨房/营销1部' union all
Select N'餐饮集团/华东区管理处/中央厨房/营销2部' union all
Select N'餐饮集团/华东区管理处/中央厨房/营销2部'
Go
--Select * from [tb]
-->SQL查询如下:
If not object_id('[fn_str]') is null
Drop function [fn_str]
Go
Create function fn_str(@col varchar(8000))
returns varchar(1000)
as
begin
if charindex('/',@col)=0
set @col=''
else
begin
set @col=stuff(@col,1,charindex('/',@col),'')+'/'
set @col=left(@col,charindex('/',@col)-1)
end
return(@col)
end
go
select dbo.fn_str(COL) col from tb
/*
col
--------------------
华东区管理处
华东区管理处
华东区管理处
华东区管理处
华东区管理处
华东区管理处
(7 行受影响)
*/
declare @s nvarchar(4000)
set @s=N'餐饮集团/华东区管理处/中央厨房/营销2部'
while CHARINDEX('/',@s+'/')>1
begin
print left(@s,charindex('/',@s)-1)
set @s=STUFF(@s,1,charindex('/',@s+'/'),N'')
end
create function f_split(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
--实现split功能 的函数
--date :2003-10-14
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>''
insert @temp values(@SourceSql)
return
end
select * from dbo.f_split('餐饮集团/华东区管理处/中央厨房/营销1部 ','/')
a
--------------------------
餐饮集团
华东区管理处
中央厨房
营销1部
(所影响的行数为 4 行)
create function f_split(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
--实现split功能 的函数
--date :2003-10-14
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>''
insert @temp values(@SourceSql)
return
end
select * from dbo.f_split('1,2,3,4',',')
a
--------------------
1
2
3
4
(所影响的行数为 4 行)