--Returns the string delimited by the nth (1/2/3 ... ) Character Changer
Use [EPICOR10]GO/** * * * object:userdefinedfunction [dbo]. [Ah_entry] Script date:2017-12-23 16:30:39 * * * * **/SETAnsi_nulls onGOSETQuoted_identifier onGO/*changed from: 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( -))returns nvarchar(Max) asbegin 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
--Returns the number of characters separated by a delimiter string
Use [EPICOR10]GO/** * * * object:userdefinedfunction [dbo]. [Ah_num_entries] Script date:2017-12-23 16:35:01 * * * * **/SETAnsi_nulls onGOSETQuoted_identifier onGO/*changed from: ice.num_entries (@char_expr nvarchar (max), @delim nchar (1)) Select Ice.num_entries (' Http://192.168.0.251/Pack Info/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( -))returns int asbegin 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][Original]-string separator function