contains Function (XQuery)

Returns a value of type xs:boolean indicating whether the value of $arg1 contains a string value specified by $arg2.

Syntax

fn:contains ($arg1 as xs:string?, $arg2 as xs:string?) as xs:boolean?

Arguments

• $arg1
  String value to test.

• $arg2
  Substring to look for.

Remarks

If the value of $arg2 is a zero-length string, the function returns True. If the value of $arg1 is a zero-length string and the value of $arg2 is not a zero-length string, the function returns False.

If the value of $arg1 or $arg2 is the empty sequence, the argument is treated as the zero-length string.

The contains() function uses XQuery's default Unicode Codepoint collation for the string comparison.

The substring value specified for $arg2 has to be less than or equal to 4000 characters. If the value specified is greater than 4000 characters, a dynamic error condition occurs and the contains() function returns an empty sequence instead of a Boolean value of True or False. SQL Server 2005 does not raise dynamic errors on XQuery expressions.

Examples

This topic provides XQuery examples against XML instances stored in various xml type columns in the AdventureWorks database. For an overview of each of these columns, see xml Data Type Representation in the AdventureWorks Database.

A. Using the contains() XQuery function to search for a specific character string

The following query finds products that contain the word Aerodynamic in its summary descriptions. The query returns the ProductID and the <Summary> element for such products.

WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS pd)
SELECT ProductModelID, CatalogDescription.query('
      <Prod>
         { /pd:ProductDescription/@ProductModelID }
         { /pd:ProductDescription/pd:Summary }
      </Prod>
 ') as Result
FROM Production.ProductModel
where CatalogDescription.value('
  contains( (/pd:ProductDescription/pd:Summary//*/text())[1], 
            "Aerodynamic")','bit')  = 1

Note the following from the previous query:

• The product model description document uses namespaces. Therefore, the namespace keyword defines the namespace prefix in the XQuery Prolog.
• The WHERE clause uses the value() method of the xml data type. Inside the value method, the XQuery contains() function is used to determine whether the <Summary> text contains the word Aerodynamic. The Boolean value returned by the contain() function is converted to a bit . This is then compared to 1.

This is the result:

ProductModelID Result
-------------- ---------
28     <Prod ProductModelID="28">
        <pd:Summary xmlns:pd= 
          "https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription">
         <p1:p xmlns:p1="http://www.w3.org/1999/xhtml">
           A TRUE multi-sport bike that offers streamlined riding and 
           a revolutionary design. Aerodynamic design lets you ride with 
          the pros, and the gearing will conquer hilly roads.</p1:p>
        </pd:Summary>
       </Prod>

See Also

Reference

XQuery Functions Against the xml Data Type

Help and Information

Getting SQL Server 2005 Assistance