如何搜索字符串并仅返回数值?

2021-09-10 00:00:00 sql tsql sql-server charindex patindex

我需要创建一个公式列.在此列中，我需要返回一个数值.

I need to create a formula column. In this column I need to return a numeric value.

示例:

database.dbo.table1 有一个名为message"的列，其中包含一条长消息.

database.dbo.table1 has a column called "message" that contains a long message.

消息的格式如下:各种单词、字符、空格<this document is>文档# = 12345 <这个文档是>

我需要做的是搜索消息，找到this document is"并在这两个短语之间搜索文档的数值，在公式列中返回文档#.

What I need to do is search through the message, find "this document is" and searched between both of those phrases for the numeric value of the document, return the document # inside the formula column.

推荐答案

使用 SQLXML XQuery 函数/方法(例如 doc.value() 通常很昂贵，应尽可能避免.在这种情况下，根据提供的信息，您可以使用 CHARINDEX.

Using SQLXML XQuery functions/methods (e.g. doc.value() is generally expensive and should be avoided when possible. In this case, based on the information provided, you can get what you need using CHARINDEX.

如果您正在使用 (n)varchar 字段，您可以这样做:

If you are working with a (n)varchar field you could do this: 

declare @mytab table (doc varchar(max))    
insert into @mytab values ('<SendDocument DocumentID="1234567">true</SendDocument>');

SELECT SUBSTRING(ci,1,CHARINDEX('"',ci)-1)
FROM (SELECT SUBSTRING(doc, CHARINDEX('DocumentID="',doc)+12,20) FROM @mytab) start(ci);

如果您正在使用 XML 字段，您可以这样做:

If you are working with an XML field you could do this:

declare @mytab table (doc xml); 
insert into @mytab values ('<SendDocument DocumentID="1234567">true</SendDocument>');

SELECT SUBSTRING(ci,1,CHARINDEX('"',ci)-1)
FROM 
(
  SELECT SUBSTRING
    (
      CAST(doc AS varchar(max)), 
      CHARINDEX('DocumentID="', CAST(doc AS varchar(max)))+12,
      20
    )
  FROM @mytab
) start(ci);

相关文章