sql - How to search a string and return only numeric value?

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

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