MS SQL获取某一字符中在字符串中出现的序数和位置

如：

DECLARE @InputString NVARCHAR(MAX) = N'RQ04-ARXXRSS-000-4315-N';

以上所示，把字符串中所有‘-’字符出现的序数及其位置。

 

DECLARE @InputString NVARCHAR(MAX) = N'RQ04-ARXXRSS-000-4315-N';
DECLARE @Specified_Character NVARCHAR(1) = '-'
DECLARE @Count INT = LEN(@InputString) - LEN(REPLACE(@InputString,@Specified_Character,''));

    
WITH cte AS (
    SELECT [String] = @InputString,
           [Number] = 1,
           [Position] = CHARINDEX(@Specified_Character, @InputString, 1)
    UNION ALL
    SELECT [String],
           [Number] + 1,
           CHARINDEX(@Specified_Character, [String], Position + 1)
    FROM cte
    WHERE [Number] < @Count AND [Position] > 0
)

SELECT * FROM cte