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))

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))

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

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