1

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.

Improve this question

2 Answers 2

Reset to default
3

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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Hi. Thanks for your reply. can you improve you function to give table structure function?
Hi! What do you mean by that? Can you please get to me more details about it?
No problem. another point. your function returns one character less. for example when I ran select dbo.SubStringByPosition(N'Startzeit_ist_von_8.00_bis_20.00_Uhr,_Abfahrt_um_23.00_Uhr' , N'_', 3) as M1 it returns 8.00_bis_20.00_Uhr,_Abfahrt_um_23.00_Uh instead of 8.00_bis_20.00_Uhr,_Abfahrt_um_23.00_Uhr please check it. thanks.
and referring to my first comment if I want to get the result something like that you can see in my question I should repeat your function two times: select N'Startzeit_ist_von_8.00_bis_20.00_Uhr,_Abfahrt_um_23.00_Uhr' as Hauptsaite, dbo.SubStringByPosition(N'Startzeit_ist_von_8.00_bis_20.00_Uhr,_Abfahrt_um_23.00_Uhr' , N'_', 3) as [Chaehd-String], LEN(dbo.SubStringByPosition(N'Startzeit_ist_von_8.00_bis_20.00_Uhr,_Abfahrt_um_23.00_Uhr' , N'_', 3)) as [Anzahl der Zeichen]
Thank you! 1. Please check my post, I fixed my function. 2. You don't need to call my function two times (it works), but much better use two selects.
1

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 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:

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

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:

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
Verspätung_von_11_bis_12_Uhr_wegen_Ölen_der_Maschine bis_12_Uhr_wegen_Ölen_der_Maschine 34
Wassermotor_und_Dampfgeneratorausfall_und_dessen_Reparatur_von_8.00_bis_12.00_Uhr,_die_auf_morgen_verschoben_wurde und_dessen_Reparatur_von_8.00_bis_12.00_Uhr,_die_auf_morgen_verschoben_wurde 76
Die_gesendeten_Spesen_wurden_vom_Arbeitgeber_nicht_genehmigt wurden_vom_Arbeitgeber_nicht_genehmigt 38

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

Improve this answer

4 Comments

Thank you for your answer! I checked your method. It's an interesting solution, but a little bit complicated to solve this simple task. Much better use a while loop.
Thanks for you reply @igdmitrov But I used while((select count(*) as cntRecs from @TempMainTable) > 0) you can find it in second part . . . Oh ok. you mean I have to use While loop in my function. ok That's great idea.
Yes, I mean my post with a function which has a little code and is readable. I added you in my network on LinkedIn.
I checked the code you sent. It ran without any problems and displayed the output I wanted. Only in terms of performance, I feel that the execution speed of this function is a bit slow @AliNajafZadeh .

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.