TSQL 查询以提取特定字符集所在字符之间的值 [英] TSQL query to extract a value between to char where a specific set of characters is there

问题描述

我有一个我似乎无法弄清楚的问题.我正在尝试从产品描述中提取容量.它总是在两个值之间，，"和盎司".但是，描述中可能包含其他逗号，它们不是我试图提取的内容的一部分.示例值为 , 15 oz., 或 , 2 oz.,

I have a problem I can't seem to figure out. I am trying to extract capacity from a product description. It is always between two values, "," and "oz." however there could be other commas included in the description that are not part of what I'm trying to extract. Example value is , 15 oz., or , 2 oz.,

我试图找到包含 oz 并且在两个逗号之间的值，但我完全没有成功.我尝试了很多方法，但这是我今天尝试过的最新方法，但我遇到了一个错误.

I'm trying to find values that have the oz in them and are between two commas and I have been completely unsuccessfully. I've tried many things, but here is the latest that I have tried today and I'm just getting an error.

SELECT SUBSTRING(
    FullDescription, 
    CHARINDEX(',', FullDescription), 
    CHARINDEX('oz.',FullDescription) 
    - CHARINDEX(',', FullDescription) 
    + Len('oz.')
)
from CatalogManagement.Product

推荐答案

您可以将 XML 拆分与 XQuery 谓词一起使用:

You might use XML-splitting together with a XQuery predicate:

DECLARE @tbl TABLE(ID INT IDENTITY, YourString VARCHAR(MAX));
INSERT INTO @tbl VALUES('Here is one with an amount, 1 oz., some more text')
                      ,('Here is one with no amount, some more text')
                      ,('a, 10 oz.')
                      ,('b, 20oz., no blank between oz and the number')
                      ,('30oz., starts with the pattern, no leading comma');

SELECT t.*
      ,A.oz.value('.','nvarchar(max)') oz
FROM @tbl t
CROSS APPLY(SELECT CAST('<x>' + REPLACE((SELECT t.YourString AS [*] FOR XML PATH('')),',','</x><x>') + '</x>' AS XML)
                       .query('/x[contains(text()[1],"oz.")]')) A(oz);

简单的想法:

• 我们使用一些字符串方法将逗号替换为 XML 标记并将您的字符串转换为 XML.每个片段都放置在一个合适的 元素中.
• 我们使用谓词只返回包含oz."的片段.

您可以使用

WHERE LEN(A.oz.value('.','nvarchar(max)'))>0

这篇关于TSQL 查询以提取特定字符集所在字符之间的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！