sql extract multiple words from one cell

I have a column that has some text written in it, including serial numbers. What I want to do is to extract the serial numbers , when they exists. I managed to extract only the first one. This works great if there is only one SN in it, otherwise the rest are not shown.

For example I have this:

1 {rtf1ansiansicpg1252deff0deflang1031{fonttbl{f0fnilfprq15fcharset0 Arial;}}  viewkind4uc1pardf0fs16 HPE Gen8 300GB 6G SAS 10K  par SN:tab 2C7713I0VG  par tab 2C7713I0WX  par tab 2C7712I0HP  par tab 2C7713I0WS  par }  
2 {rtf1ansiansicpg1252deff0deflang1031{fonttbl{f0fnilfprq15fcharset0 Arial;}}  viewkind4uc1pardf0fs16 HGST Ultrastar 7K4000 2TB HDD  par SN:tab P5JKS38W  par }  
3 {rtf1ansiansicpg1252deff0deflang1031{fonttbl{f0fnilfprq15fcharset0 Arial;}}  viewkind4uc1pardf0fs16 Microsoft Project Standard 2016  par SN:tab 3BN8W-YBKJQ-3JF76-GKHVV-PWFPJ  par ID:tab ObenAT1d0220  par }
4 {rtf1ansiansicpg1252deff0deflang1031{fonttbl{f0fnilfprq15fcharset0 Arial;}}  viewkind4uc1pardf0fs16 ClientID:tab AuttechGR0201  par SN:tab 737NH-HVYGX-QYTFF-VGDG4-HQWCG  par }
5 {rtf1ansiansicpg1252deff0deflang1031{fonttbl{f0fnilfprq15fcharset0 Arial;}}  viewkind4uc1pardf0fs16 HGST Ultrastar 7K4000 2TB HDD }

I need to split the each SN into new row, like this:

SerialNumbers
2C7713I0VG
2C7713I0WX
2C7712I0HP
2C7713I0WS
P5JKS38W
3BN8W-YBKJQ-3JF76-GKHVV-PWFPJ
737NH-HVYGX-QYTFF-VGDG4-HQWCG    

This is what I did so far:

SELECT 
ABFPosLangText, 
SUBSTRING(
    ABFPosLangText, 
    CHARINDEX('par SN:tab', ABFPosLangText)+ LEN('par SN:tab'), --begining of the first serial number
    CHARINDEX('', ABFPosLangText, CHARINDEX('par SN:tab', ABFPosLangText))- CHARINDEX('par SN:tab', ABFPosLangText)+ LEN('par SN:tab')

FROM ABFPos

 WHERE ABFPosLangText LIKE ('%SN:%')

As it can be seen , the serial nums are in between tab and par.

I used par SN:tab – because I was trying to get any result

I am still a newbie when it comes to SQL, so any help would be great.

EDIT

using the answer from @Tyron78 I tried to get my result, but it still gives me errors. I do have to point out that I did change the code a little bit because I use MS SQL – instead of TRIM func I used RTRIM(LTRIM()) – which should work – but I still get the first part of the text in the result.

When the query is implemented on the first line I get the following result:

SerialNr
{rtf1ansiansicpg1252deff0deflang1031{fonttbl{f0fnilfprq15fcharset0 Arial;}} viewkind4uc1df0fs16 HPE Gen8 300GB 6G SAS 10K  SN:
 2C7713I0VG
 2C7713I0WX
 2C7712I0HP
 2C7713I0WS  }

AND this is the msg I get : Message 9421, level 16, status 1, line 1 XML analysis: line 2, character 48, invalid name character.

Answer

You can try and use a mixture of XML and CrossApply, such as follows:

DECLARE @t TABLE(
ID int, MyString nvarchar(1000)
)

INSERT INTO @t VALUES
(1, N'{rtf1ansiansicpg1252deff0deflang1031{fonttbl{f0fnilfprq15fcharset0 Arial;}}  viewkind4uc1pardf0fs16 HPE Gen8 300GB 6G SAS 10K  par SN:tab 2C7713I0VG  par tab 2C7713I0WX  par tab 2C7712I0HP  par tab 2C7713I0WS  par }')
,(2, N'{rtf1ansiansicpg1252deff0deflang1031{fonttbl{f0fnilfprq15fcharset0 Arial;}}  viewkind4uc1pardf0fs16 HGST Ultrastar 7K4000 2TB HDD  par SN:tab P5JKS38W  par }')
,(3, N'{rtf1ansiansicpg1252deff0deflang1031{fonttbl{f0fnilfprq15fcharset0 Arial;}}  viewkind4uc1pardf0fs16 HGST Ultrastar 7K4000 2TB HDD}')

;WITH cte1 AS(
SELECT ID, SUBSTRING(MyString, CHARINDEX('SN:', MyString)+3, LEN(MyString)-CHARINDEX('SN:', MyString)-2) AS MyString
  FROM @t
  WHERE CHARINDEX('SN:', MyString) > 0
)
select 
    a.value('.', 'varchar(max)') AS SerialNr
from
    (select cast('<M>' + rtrim(ltrim(REPLACE(REPLACE(REPLACE(REPLACE(MyString, 'tab', '</M><M>'),'par',''),'{',''),'}',''))) + '</M>' AS XML) as col from cte1) as A
    CROSS APPLY A.col.nodes ('/M') AS Split(a)
where NULLIF(a.value('.', 'varchar(max)'),'') IS NOT NULL

However, this will only work if SN is the last part of your string. If anything else follows in the string, you will have to consider this in your substring in cte1.