从第三个或第四个特定字符到字符串末尾获取子字符串

Question

我在表中有许多记录，如下所示，其中我为您放置了表的结构和冲突的记录。

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]

这是我的插入查询：

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

问题出在哪里？实际上，我需要一个函数，用它可以输入一个字符及其重复位置(例如_和3(，从_字符所在的第三个位置到字符串的末尾，并向我显示从_到末尾有什么字符。结构应该是这样的：

Chaehd字符串Startzeit_ist_von_8.00_bis_20.00_Uhr，_Abfahrt_um_23.00_Uhr8.00_bis _20.00_Uhr40

Answer 1

我编写了以下函数：

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;

尝试在您的查询中使用此功能：

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

如果您需要构建表作为示例，请使用以下代码：

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

Answer 2

尝试以下功能：

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
)

根据您的请求，您想要一个Table-Valued函数。如何使用：

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

结果：

Chaehd字符串Startzeit_ist_von_8.00_bis_20.00_Uhr，_Abfahrt_um_23.00_Uhr8.00_bis _20.00_Uhr40