The following code will work starting with MS SQL Server 2016:

declare @s nvarchar(max) = N's:6:"module";s:11:"leadCapture";s:6:"action";s:5:"save2";';

select row_number() over(order by r.Ordinal, v.[key]) as [RN],
    v.value as [SubStringValue]
from (
    select t.[key] as [Ordinal], t.value as [Value]
    from openjson('["' + replace(string_escape(@s, 'json'), ';', '","') + '"]', '$') t
    where t.[key] <= 1
) r
    cross apply openjson('["' + replace(string_escape(r.Value, 'json'), ':', '","') + '"]', '$') v
where v.[key] > 0
order by RN;

Ugly and convoluted, but at the time of writing this is your only option if you want to do it in pure SQL in an on-prem instance. The alternatives are:

• Azure SQL (both SQL Database and Managed Instance) have an improved version of the string_split() function, which accepts an additional parameter, enable_ordinal . This will add an extra column into its output containing ordinal position of substrings within a string. If you use Azure version of SQL, or somehow have SQL Server 2022, this will allow you to avoid having to deal with JSON.
• CLR function. Pretty much the best solution when it comes to string manipulation in SQL Server, but can be a pain to maintain.
• Cleansing by some ETL tool (SSIS, ADF, AirFlow, whatever) before the data enters the database.