RaqForum 41 No.
6 View  • 
     ...

From SQL to SPL: Substring from a column of strings

 

The database table tbl has a string field DESCRIPTION.

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

Now we need to retrieve the word 'EN' and the subsequent string of numbers from the DESCRIPTION field. The string of numbers may consist entirely of digits, such as '10204', or it may contain special characters, such as '10277/10'. Caution: Do not retrieve punctuation marks; If the string does not contain 'EN', return null.

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

SQL： 

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;

SQL can implement it using CROSS APPLY with XML syntax, but the code is complex and lengthy. Using regular expressions can also implement it, but the code is more difficult to understand. SPL provides string splitting functions and ordered calculation functions, with simple and easy to understand code:

　A

1

$select * from tbl.txt

2

=A1.new(ARTIKELNR, if(s=substr(DESCRIPTION,"EN  ").split().select@c(pos("012346789/+-%_",~)).concat(),"EN "+s):NORM)

A1： Load data.

A2： Find the substring after "EN " from the large string, split it into a set by character, extract the preceding numeric members, as well as special characters such as +-%_ in the numeric string, and merge them into a numeric string. The select function is used for filtering, @c represents taking from the first member onward until the first member that makes the condition false. The pos function returns the position of the substring.

Question source：https://stackoverflow.com/questions/78159534/fetch-values-from-previous-non-null-value-rows


 
 •  6 View  •