SQL Substring from a column of strings

Question

I have a table with around 100,000 articles.

Each article has a description.

short example:

ARTIKELNR	DESCRIPTION
104009400031900	S235JRG2C+C EN 10277/10 80 x 8 mm Blanker Flachstahl
104009800002950	Werksattest nach EN 10204
105009400092360	EN 10060 Inox Rund Ø 28 mm WNr1.4034 warmgefertigt
105009400068571	90 mm WNr1.4404 warmgefertigt, EN 10060, geschmiedet
105009400004420	WNr1.4301 Inox Vierkantstahl 6 x 6 mm EN 10278, blankgezogen
105009400008800	WNr1.4301 Inox Band geschnitten 25 x 4 mm, Breitentol. 25 m
105009400068600	WNr1.4112 Inox Rund 8.00 mm nach EN 10088-3 + A+C+SL, gesch
105009400068700	WNr1.4301 Inox Rund 36.00 mm nach EN 10088-3, geschliffen, T
105009400068800	WNr1.4104 Inox Rund 25.00 mm nach EN 10088-3, geschliffen, T
105009400010035	WNr1.4404 Inox Vierkantstahl 30 x 30 mm, gezogen EN 10278

None have the same format.

What I would like to do is extract the "NORM" from the description.

So, I would like to have the following result

ARTIKELNR	NORM
104009400031900	EN 10277/10
104009800002950	EN 10204
105009400092360	EN 10060
105009400068571	EN 10060
105009400004420	EN 10278
105009400008800
105009400068600	EN 10088-3
105009400068700	EN 10088-3
105009400068800	EN 10088-3
105009400010035	EN 10278

Answer 1

Please try the following solution leveraging SQL Server XML and XQuery functionality.

It will work starting from SQL Server 2012 onwards.

Notable points:

• CROSS APPLY is tokenizing DESCRIPTION column as XML.
• XQuery .query() method is looking for the "EN" token via FLWOR expression and the following token via XPath predicate ...r[position()=($pos, $pos + 1)].
• XQuery .value() method gives us a desired output string.
• REPLACE() function removes optional trailing comma.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, ARTIKELNR CHAR(15), DESCRIPTION NVARCHAR(1024));
INSERT INTO @tbl (ARTIKELNR, DESCRIPTION) VALUES
(104009400031770,N'725528 MOTORKONSOLE KM35 EN SENKKLAPPFENSTER WICSKY 3'), -- Exception case, no digits after EN
(104009400031900,N'S235JRG2C+C EN10088-3 80 x 8 mm Blanker Flachstahl'),    -- Exception case, no space after EN
(104009400031900,N'S235JRG2C+C EN 10277/10 80 x 8 mm Blanker Flachstahl'),
(104009800002950,N'Werksattest nach EN 10204'),
(105009400092360,N'EN 10060 Inox Rund Ø 28 mm WNr1.4034 warmgefertigt'),
(105009400068571,N'90 mm WNr1.4404 warmgefertigt, EN 10060, geschmiedet'),
(105009400004420,N'WNr1.4301 Inox Vierkantstahl 6 x 6 mm EN 10278, blankgezogen'),
(105009400008800,N'WNr1.4301 Inox Band geschnitten 25 x 4 mm, Breitentol. 25 m'),
(105009400068600,N'WNr1.4112 Inox Rund 8.00 mm nach EN 10088-3 + A+C+SL, gesch'),
(105009400068700,N'WNr1.4301 Inox Rund 36.00 mm nach EN 10088-3, geschliffen, T'),
(105009400068800,N'WNr1.4104 Inox Rund 25.00 mm nach EN 10088-3, geschliffen, T'),
(105009400010035,N'WNr1.4404 Inox Vierkantstahl 30 x 30 mm, gezogen EN 10278');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = SPACE(1);

SELECT *
    , REPLACE(c.query('
          for $x in /root/r[text()="EN"]
          let $pos := count(root/r[. << $x]) + 1
          return if (xs:int(substring((/root/r[$pos + 1]/text())[1],1,5)) instance of xs:int) then
                data(/root/r[position()=($pos, $pos + 1)])
            else data(/root/r[$pos])
        ').value('text()[1]', 'NVARCHAR(30)')
        ,',', '') AS NORM
FROM @tbl AS t
CROSS APPLY (SELECT PATINDEX('%EN[0-9][0-9][0-9][0-9][0-9]%', DESCRIPTION)) AS t2(pos)  -- to handle Exception cases
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
    REPLACE(IIF(pos > 0, STUFF(DESCRIPTION,pos + 2,0, SPACE(1)), DESCRIPTION), @separator, ']]></r><r><![CDATA[') + 
    --REPLACE(DESCRIPTION, @separator, ']]></r><r><![CDATA[') + 
    ']]></r></root>' AS XML)) AS t1(c)
ORDER BY ID;

Output

ID	ARTIKELNR	DESCRIPTION	NORM
1	104009400031770	725528 MOTORKONSOLE KM35 EN SENKKLAPPFENSTER WICSKY 3	EN
2	104009400031900	S235JRG2C+C EN10088-3 80 x 8 mm Blanker Flachstahl	EN 10088-3
3	104009400031900	S235JRG2C+C EN 10277/10 80 x 8 mm Blanker Flachstahl	EN 10277/10
4	104009800002950	Werksattest nach EN 10204	EN 10204
5	105009400092360	EN 10060 Inox Rund Ø 28 mm WNr1.4034 warmgefertigt	EN 10060
6	105009400068571	90 mm WNr1.4404 warmgefertigt, EN 10060, geschmiedet	EN 10060
7	105009400004420	WNr1.4301 Inox Vierkantstahl 6 x 6 mm EN 10278, blankgezogen	EN 10278
8	105009400008800	WNr1.4301 Inox Band geschnitten 25 x 4 mm, Breitentol. 25 m	NULL
9	105009400068600	WNr1.4112 Inox Rund 8.00 mm nach EN 10088-3 + A+C+SL, gesch	EN 10088-3
10	105009400068700	WNr1.4301 Inox Rund 36.00 mm nach EN 10088-3, geschliffen, T	EN 10088-3
11	105009400068800	WNr1.4104 Inox Rund 25.00 mm nach EN 10088-3, geschliffen, T	EN 10088-3
12	105009400010035	WNr1.4404 Inox Vierkantstahl 30 x 30 mm, gezogen EN 10278	EN 10278