"数据库表 tbl 有个字符串字段 DESCRIPTION。 ARTIKELNR DESCRIPTION 104009400031900 S235JRG2C+C EN 10277/10 80 x .."

jinxing
乾学院 53 号会员
1 回帖 • 50 浏览 • 4 天前

从 SQL 到 SPL：Substring from a column of strings

应用计算

数据库表 tbl 有个字符串字段 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

现在要从 DESCRIPTION 字段取出单词 "EN" 及后面的数字串，数字串可能全是数字，比如 "10204"，也可能含有特殊字符，比如 "10277/10"。注意：不要取标点符号；如果字符串不含 "EN"，则返回 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使用 CROSS APPLY 配合 XML 语法可实现，代码复杂冗长。改用正则表达式也可实现，但代码更难懂。SPL 提供了字符串拆分函数，以及有序计算函数，代码简单易懂：

	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：加载数据。

A2：从大字符串里找到 "EN" 后面的子串，按字符拆分成集合，取出前面的数字成员，以及 +-%_ 等数字串里的特殊字符，合并为数字串。函数 select 用于过滤，@c 表示从第一个成员往后取，直到第一个使条件为假的成员停止。函数 pos 返回子串的位置。

问题来源：https://stackoverflow.com/questions/78159534/fetch-values-from-previous-non-null-value-rows