How to search a string and return only numeric val

Question

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

Example:

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

The message is formatted as so: various words, characters, spaces <this document is> document# = 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.

Answer 1

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.

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

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

Answer 2

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

select      doc.value('(//@DocumentID)[1]','int')
from        @mytab

---

If it is not saved as XML but as VARCHAR

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

select      cast (doc as xml).value('(//@DocumentID)[1]','int')
from        @mytab