I have a number of records in the table as shown below, where I have put the structure of the table and the records that are in conflict for you.

CREATE TABLE [dbo].[TblHilfeStrings]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [HilfeStrings] [varchar](200) NOT NULL,

    CONSTRAINT [PK_TblHilfeStrings] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
                    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
                          ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

And this is my insert query:

SET IDENTITY_INSERT [dbo].[TblHilfeStrings] ON

INSERT INTO [dbo].[TblHilfeStrings] ([Id], [HilfeStrings]) 
VALUES (1, N'Startzeit_ist_von_8.00_bis_20.00_Uhr,_Abfahrt_um_23.00_Uhr')
INSERT INTO [dbo].[TblHilfeStrings] ([Id], [HilfeStrings]) 
VALUES (2, N'Verspätung_von_11_bis_12_Uhr_wegen_Ölen_der_Maschine')
INSERT INTO [dbo].[TblHilfeStrings] ([Id], [HilfeStrings]) 
VALUES (3, N'Wassermotor_und_Dampfgeneratorausfall_und_dessen_Reparatur_von_8.00_bis_12.00_Uhr,_die_auf_morgen_verschoben_wurde')
INSERT INTO [dbo].[TblHilfeStrings] ([Id], [HilfeStrings]) 
VALUES (4, N'Die_gesendeten_Spesen_wurden_vom_Arbeitgeber_nicht_genehmigt')

SET IDENTITY_INSERT [dbo].[TblHilfeStrings] OFF

And what is the problem? Actually, I need a function with which I can enter a character and its repetition place (for example _ and 3) from the third place where the _ character is located to the end of the string and show me what There are characters from _ to the end. The structure should look something like this:

Meanwhile, I searched a lot on the Stack Overflow website, but I did not find anything similar to this request.

CodePudding user response：

I wrote this function:

CREATE FUNCTION dbo.SubStringByPosition (@text varchar(256), @search_exp varchar(1), @position int)
RETURNS varchar(256)
WITH EXECUTE AS CALLER
AS
BEGIN
    declare @len int = 0;
    declare @i int = 0;
    declare @pos int = 0;

    set @len = len(@text);

    while(@i < @position and @pos < @len)
    begin
        set @pos = charindex(@search_exp, @text, @pos   1)
        set @i = @i   1
    end
    
    RETURN(substring(@text, @pos   1, @len - @pos - 1));
END;

Try to use this function in your query:

SELECT  [Id], [HilfeStrings], dbo.SubStringByPosition(a.HilfeStrings, '_', 3) AS SubStr
FROM [TestDb].[dbo].[TblHilfeStrings] a

CodePudding user response：

Try following function:

CREATE FUNCTION [dbo].[GetStringAfterSpecificCharacter]
(
    @MainString NVARCHAR(4000),
    @DelimiterCharacter NCHAR(1),
    @DelimiterPlace int
)
RETURNS Table
AS 
RETURN
(

WITH SplitedStrings(Ends,Endsp)
    AS (
        SELECT 0 AS Ends, CHARINDEX(@DelimiterCharacter,@MainString) AS Endsp
        UNION ALL
        SELECT Endsp 1, CHARINDEX(@DelimiterCharacter,@MainString,Endsp 1)
            FROM SplitedStrings
            WHERE Endsp > 0
    )
        SELECT f.Hauptsaite, f.DataStr as [Chaehd-String], LEN(f.DataStr) as [Anzahl der Zeichen]
    FROM (
        SELECT 'RowId' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'DataStr' = SUBSTRING(@MainString,Ends,LEN(@MainString)-(1 - Ends)),
        Hauptsaite = @MainString 
        FROM SplitedStrings
    ) f WHERE f.RowId = @DelimiterPlace   1
)

Based on your request, you wanted a function it returns value is of the table or Table Valued Function. How to use:

select * from [dbo].[GetStringAfterSpecificCharacter] (N'Startzeit_ist_von_8.00_bis_20.00_Uhr,_Abfahrt_um_23.00_Uhr', N'_', 3)

The result:

With the following method, you can have the result on the main table and run the function on the table and have the complete output for all the records of the main table:

declare @DelimiterCharacter NCHAR(1) = N'_',    @DelimiterPlace int = 3
declare @TempTable as table 
(
Hauptsaite varchar(4000) , 
[Chaehd-String] varchar(4000), 
[Anzahl der Zeichen] int
)

declare @TempMainTable as table 
(
[Id] [int] NULL,
[HilfeStrings] [varchar](200) NULL
)

insert into @TempMainTable 
select * from [dbo].[TblHilfeStrings]

while((select count(*) as cntRecs from @TempMainTable) > 0)
begin
insert into @TempTable (Hauptsaite , [Chaehd-String] , [Anzahl der Zeichen])
SELECT d.Hauptsaite , d.[Chaehd-String], d.[Anzahl der Zeichen] from  [dbo].[GetStringAfterSpecificCharacter]((select top(1) t1.HilfeStrings  from @TempMainTable t1 order by t1.Id) , @DelimiterCharacter , @DelimiterPlace) d
    delete from @TempMainTable where Id = (select top(1) Id  from @TempMainTable order by Id)
end
select te.Hauptsaite, te.[Chaehd-String], te.[Anzahl der Zeichen] from @TempTable te

The result:

If you find the answer useful, please tick it as a favorite answer.