Get Sub-String from third or forth specific character to end of string

Question

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:

Hauptsaite	Chaehd-String	Anzahl der Zeichen
Startzeit_ist_von_8.00_bis_20.00_Uhr,_Abfahrt_um_23.00_Uhr	8.00_bis_20.00_Uhr,_Abfahrt_um_23.00_Uhr	40

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

Answer 1

I wrote following 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));
END;

Try to use this function in your query:

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

if you need to build table as your example, use this code:

SELECT AA.*, LEN(AA.[Chaehd-String]) AS 'Anzahl der Zeichen' FROM (
SELECT [HilfeStrings] AS 'Hauptsaite', dbo.SubStringByPosition(a.HilfeStrings, '_', 3) AS 'Chaehd-String'
FROM [dbo].[TblHilfeStrings] a) AS AA