[Sql Server][原创] - 字符串分隔函数
时间：2021-07-01 10:21:17
帮助过：4人阅读
[EPICOR10]
GO
/****** Object: UserDefinedFunction [dbo].[AH_Entry] Script Date: 2017-12-23 16:30:39 ******/
SET ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER
ON
GO
/*
改自：Ice.Entry(@idx int, @char_expr nvarchar(max), @delim nchar(1))
select ice.Entry(1,‘http://192.168.0.251/PackInfo/Tiger/FM/TG17/PC/Chest Strap/A1.jpg‘,‘Tiger/‘)
select dbo.AH_Entry(2,‘http://192.168.0.251/PackInfo/Tiger/FM/TG17/PC/Chest Strap/A1.jpg‘,‘Tiger/‘)
select SubString(dbo.AH_Entry(dbo.AH_Num_Entries(‘http://192.168.0.251/PackInfo/Tiger/FM/TG17/PC/Chest Strap/A1.jpg‘,‘Tiger/‘),‘http://192.168.0.251/PackInfo/Tiger/FM/TG17/PC/Chest Strap/A1.jpg‘,‘Tiger/‘),0,CharIndex(‘/‘,dbo.AH_Entry(dbo.AH_Num_Entries(‘http://192.168.0.251/PackInfo/Tiger/FM/TG17/PC/Chest Strap/A1.jpg‘,‘Tiger/‘),‘http://192.168.0.251/PackInfo/Tiger/FM/TG17/PC/Chest Strap/A1.jpg‘,‘Tiger/‘)))
*/
ALTER function [dbo].
[AH_Entry](
@idx int,
@char_expr nvarchar(
max),
@delim nvarchar(
100))
returns nvarchar(
max)
as
begin
declare @begin int,
@end int,
@count int
declare @str nvarchar(
max)
set @count = 1
set @begin = 1
set @end = charindex(
@delim,
@char_expr)
if @idx <= 0
return (
NULL)
if (
@end = 0)
and @idx > 1
return (
NULL)
if (
@end = 0)
and @idx = 1
return @char_expr
set @str = @char_expr
while charindex(
@delim,
@str)
> 0 and @count < @idx
begin
set @begin = charindex(
@delim,
@str)
+ len(
@delim)
set @str = substring(
@str,
@begin,
len(
@str)
- @begin + 1)
set @count = @count + 1
end
if @count < @idx
return (
NULL)
if charindex(
@delim,
@str)
> 0
set @str = substring(
@str,
1,
charindex(
@delim,
@str)
- 1)
else
set @str = substring(
@str,
1,
len(
@str))
return (
@str)
end
-- 返回字符串以分隔符分隔的字符数
USE [EPICOR10]
GO
/****** Object: UserDefinedFunction [dbo].[AH_Num_Entries] Script Date: 2017-12-23 16:35:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
改自：Ice.num_entries(@char_expr nvarchar(max), @delim nchar(1))
select ice.num_entries(‘http://192.168.0.251/PackInfo/Tiger/FM/TG17/PC/Chest Strap/A1.jpg‘,‘Tiger/‘)
select dbo.AH_Num_Entries(‘http://192.168.0.251/PackInfo/Tiger/FM/TG17/PC/Chest Strap/A1.jpg‘,‘Tiger/‘)
select dbo.AH_Num_Entries(‘http://192.168.0.251/PackInfo/Tiger/FM/TG17/Tiger/PC/Chest Strap/A1.jpg‘,‘Tiger/‘)
*/
ALTER function [dbo].[AH_Num_Entries](@char_expr nvarchar(max), @delim nvarchar(100))
returns int
as
begin
declare @begin int, @count int
declare @str nvarchar(max)
set @count = 1
set @begin = 1
if len(@char_expr) <= 0
return (0)
set @str = @char_expr
while charindex(@delim, @str) > 0
begin
set @begin = charindex(@delim, @str) + len(@delim)
set @str = substring(@str, @begin, len(@str) - @begin + 1)
set @count = @count + 1
end
return (@count)
end
[Sql Server][原创] - 字符串分隔函数
标签：arc sele nvarchar function body object pos expr style
