Function to get a Sub String after a selected character in place selectively and dynamically-CodePudding

In one of the database tables, I have a string type field that contains a series of special strings combined with some special characters. For example:

'HGHGSD_JHJSD_HGSDHGJD_GFSDGFSHDGF_GFSD'

'SJDGh-SUDYSUI-jhsdhsj-YTsagh-ytetyyuwte-sagd'

'hwerweyri~sdjhfkjhsdkjfhds~jsdfhjsdhf~mdnfsd,mfn'

Based on a formula, a sub string is always returned after the special character. But this string may be after the first, second or third place of the special character - or _ or ~. I used Charindex and Substring function in SQL server. But always only the first part of the character string after the selected character is returned. for example:

select SUBSTRING ('hwerweyri~sdjhfkjhsdkjfhds~jsdfhjsdhf~mdnfsd,mfn', 0, CHARINDEX('~', 'hwerweyri~sdjhfkjhsdkjfhds~jsdfhjsdhf~mdnfsd,mfn', 0))

returned value: hwerweyri If there is a solution for this purpose or you have a piece of code that can work in solving this problem, please advise. It is important to mention that the location of the special character must be entered by ourselves in the function, for example, after the third repetition or the second repetition or the tenth repetition. The method or code should be such that the location can be entered dynamically and the function does not need to be defined statically. For Example:

'HGHGSD_JHJSD_HGSDHGJD_GFSDGFSHDGF_GFSD' ==> 3rd substring ==> 'GFSDGFSHDGF'
'HGHGSD_JHJSD_HGSDHGJD_GFSDGFSHDGF_GFSD' ==> second substring ==> 'HGSDHGJD'
'HGHGSD_JHJSD_HGSDHGJD_GFSDGFSHDGF_GFSD' ==> 1st substring ==> 'JHJSD'

And The formula will be sent to the function through a programmed form and the generated numbers will be numbers between 1 and 15. These numbers are actually the production efficiency of a product whose form is designed in C# programming language. These numbers sent to the function are variable and each time these numbers may be sent to the function and applied to the desired character string. The output should look something like the one above. I don't know if I managed to get my point across or if I managed to make my request correctly or not.

CodePudding user response：

Try bellow function:

CREATE FUNCTION [dbo].[SplitWithCte]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1),
    @PlaceOfDelimiter int
)RETURNS Table
as 
return
(
WITH SplitedStrings(Ends,Endsp)
    AS(
        SELECT 0 AS Ends, CHARINDEX(@Delimiter,@String) AS Endsp
        UNION ALL
        SELECT Endsp 1, CHARINDEX(@Delimiter,@String,Endsp 1)
            FROM SplitedStrings
            WHERE Endsp > 0
    )
    select f.DataStr from  (
    SELECT 'RowId' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'DataStr' = SUBSTRING(@String,Ends,COALESCE(NULLIF(Endsp,0),LEN(@String) 1)-Ends)
    FROM SplitedStrings ) f where f.RowId = @PlaceOfDelimiter   1
)

How to use:

select * from [dbo].[SplitWithCte](N'HGHGSD_JHJSD_HGSDHGJD_GFSDGFSHDGF_GFSD', N'_', 3)

select DataStr from [dbo].[SplitWithCte](N'HGHGSD_JHJSD_HGSDHGJD_GFSDGFSHDGF_GFSD', N'_', 3)

Result : GFSDGFSHDGF

CodePudding user response：

Please use one loop to handle those string type field.

DEMO


CREATE FUNCTION [dbo].[Fun_SplitStr]
(
    @originalStr      VARCHAR(8000), -- String to be split
    @split varchar(100)              -- Separator
)
RETURNS @temp TABLE(Result VARCHAR(100))
AS
BEGIN
    DECLARE @result AS VARCHAR(100); -- Define variables for receiving individual results
 
    SET @originalStr = @originalStr   @split ;
 
    WHILE (@originalStr <> '')
    BEGIN
        SET @result = LEFT(@originalStr, CHARINDEX(@split, @originalStr, 1) -1) ;
 
        INSERT @temp VALUES(@result) ;
 
        --STUFF() function is used to delete characters of a specified length and can insert another set of characters at the specified starting point.
        SET @originalStr = STUFF(@originalStr, 1, CHARINDEX(@split, @originalStr, 1), '');
    END
    RETURN
END

added

Create function [dbo].[F_SplitOfIndex]
 (
  @String nvarchar(max), 
  @split nvarchar(10), 
  @index int
 )
 returns nvarchar(1024)
 as
 begin
  declare @location int
  declare @start int
  declare @next int
  declare @seed int
  
  set @String=ltrim(rtrim(@String))
  set @start=1
  set @next=1
  set @seed=len(@split)   
  set @location=charindex(@split,@String)
  
  while @location<>0 and @index>@next
  begin
    set @start=@location @seed
    set @location=charindex(@split,@String,@start)
    set @next=@next 1
  end
  if @location =0 select @location =len(@String) 1   
  return substring(@String,@start,@location-@start)
 end

select dbo.F_SplitOfIndex('111,b2222,323232,32d,e,323232f,g3222',',', 3)

output: 323232