TSQL从已知结尾的字符串中提取数字范围

Question

我需要从一段文本中提取一个数字。这个数字不总是出现在同一个位置，但它总是以U结尾。

例子:

Products  
----------------------------------------------
PRODUCT/100U/PRODUCT/1000A/10000E  
PRODUCT/100000A/PRODUCT/10U
PRODUCT/ITEM/1000U/PRODUCT/100000000E

我想返回上面行中的100、10和1000。我尝试使用SUBSTRING与PATINDEX以及%[0-9]u%通配符，但数字值不总是单个数字。

在@dfundako发布他的答案之前，我提出了另一个解决方案，并使用他的SUBSTRING长度逻辑。它返回与他相同的结果，但删除了U和其后的所有字符。谢谢您的帮助！

SELECT SUBSTRING(SUBSTRING(@PRODUCT,PATINDEX('%/[0-9]%u%',@PRODUCT)+1,CHARINDEX('/U', REVERSE(@PRODUCT])) +2),0,CHARINDEX('u',SUBSTRING(@PRODUCT,PATINDEX('%/[0-9]%u%',@PRODUCT)+1,CHARINDEX('/U', REVERSE(@PRODUCT])) +2),0))

- JWeatherman

Answer 1

这看起来很糟糕，但它确实有效。首先反转字符串，找到'/U'（如果没有因为U值在末尾，它仍然可以工作），找到下一个'/'，取这两个位置，提取它们之间的子字符串，并反转该值。如果每个字符串有多个U值，这种方法将不起作用。您需要循环执行它。

DECLARE @test1 VARCHAR(100) = 'PRODUCT/100U/PRODUCT/1000A/10000E'
DECLARE @test2 VARCHAR(100) = 'PRODUCT/100000A/PRODUCT/10U'
DECLARE @test3 VARCHAR(100) = 'PRODUCT/ITEM/1000U/PRODUCT/100000000E'
DECLARE @test4 VARCHAR(100) = 'PRODUCT/100000A/PRODUCT/423587U'

--Reverse the string to find '/U'  
----We want to do this to find the 'Starting position' for the SUBSTRING function
----Add 2 to get past the /U
SELECT REVERSE(@test1)
SELECT CHARINDEX('/U', REVERSE(@test1))+2

--Find the next '/' in the reversed string using the /U location as the starting location
----Have to add 1 to get past the length of '/'
SELECT CHARINDEX('/', REVERSE(@test1), CHARINDEX('/U', REVERSE(@test1))+1)

--Get the string between these two values
----Need the reversed string, the starting position, and the difference between the end and start(meaning length of the string to return) 
----Again, have to do a little adjustment to the numbers to remove the / and /U 
SELECT SUBSTRING(REVERSE(@test1), CHARINDEX('/U', REVERSE(@test1))+2, CHARINDEX('/', REVERSE(@test1), CHARINDEX('/U', REVERSE(@test1))+1) - CHARINDEX('/U', REVERSE(@test1))-2)

--Reverse the whole thing to get the final value
SELECT REVERSE(SUBSTRING(REVERSE(@test1), CHARINDEX('/U', REVERSE(@test1)) +2, CHARINDEX('/', REVERSE(@test1), CHARINDEX('/U', REVERSE(@test1))+1) - CHARINDEX('/U', REVERSE(@test1))-2))


SELECT REVERSE(SUBSTRING(REVERSE(@test1), CHARINDEX('/U', REVERSE(@TEST1)) +2, CHARINDEX('/', REVERSE(@TEST1), CHARINDEX('/U', REVERSE(@TEST1))+1) -CHARINDEX('/U', REVERSE(@TEST1))-2))
SELECT REVERSE(SUBSTRING(REVERSE(@TEST2), CHARINDEX('/U', REVERSE(@TEST2)) +2, CHARINDEX('/', REVERSE(@TEST2), CHARINDEX('/U', REVERSE(@TEST2))+1) -CHARINDEX('/U', REVERSE(@TEST2))-2))
SELECT REVERSE(SUBSTRING(REVERSE(@TEST3), CHARINDEX('/U', REVERSE(@TEST3)) +2, CHARINDEX('/', REVERSE(@TEST3), CHARINDEX('/U', REVERSE(@TEST3))+1) -CHARINDEX('/U', REVERSE(@TEST3))-2))
SELECT REVERSE(SUBSTRING(REVERSE(@TEST4), CHARINDEX('/U', REVERSE(@TEST4)) +2, CHARINDEX('/', REVERSE(@TEST4), CHARINDEX('/U', REVERSE(@TEST4))+1) -CHARINDEX('/U', REVERSE(@TEST4))-2))

- dfundako

---

嗯，这可以被简化一下；-) - Shnugo

Answer 2

另一个解决方案稍微简单一点，但仍然有一点复杂；-)

DECLARE @tbl TABLE(Products VARCHAR(100));  
INSERT INTO @tbl VALUES
 ('PRODUCT/100U/PRODUCT/1000A/10000E')
,('PRODUCT/100000A/PRODUCT/10U')
,('PRODUCT/ITEM/1000U/PRODUCT/100000000E');

--查询

WITH cte AS
(
    SELECT Products
          ,REVERSE(SUBSTRING(Products,1,PATINDEX('%[0-9]u/%',Products + '/'))) AS CutEndReverse
    FROM @tbl
)
SELECT Products
      ,REVERSE(SUBSTRING(CutEndReverse,1,CHARINDEX('/',CutEndReverse)-1)) AS YourNumber
FROM cte

- Shnugo

Answer 3

试试这个

SELECT REPLACE(Products,'U','') AS Products
FROM
(
SELECT Split.a.value('.','nvarchar(max)') AS Products
FROM
(
SELECT CAST( '<S>'+REPLACE(Products,'/','</S><S>')+'</S>'  AS XML ) AS Products
FROM @tbl
)AS A
CROSS APPLY Products.nodes('S') AS Split(a)
)dt 
WHERE Products LIKE '%U'

结果

Products
100
10
1000

- Sreenu131

---

这取决于假设没有其他以“U”结尾的项。我不知道，但我认为PRDUCT可能是产品名称的占位符（与ITEM相同）...如果其中一个以U结尾，那么这将失败。 - Shnugo