How to search a string and return only numeric value?

253 views Asked by At

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.

2

There are 2 answers

1
Alan Burstein On BEST ANSWER

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);
1
David דודו Markovitz On
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